SUMIF with criteria as a number

Options
✭✭✭✭✭

I'm trying to do a simple SUMIF formula but it isn't working and I think it is because I'm trying to use a number as the criteria. But I would think there is a workaround.

My main sheet has data like the below, where the same C/M is listed multiple times with different actual amounts.

On a separate sheet, I want to sum all the actual amounts for each C/M and tried the below but it results in a \$0. But the result should be \$17.00.

Any thoughts on what I am doing wrong?

• ✭✭✭✭✭✭
Options

Hope you are fine, please try the following formula:

=SUMIFS({Actual}, {C/M}, @cell = [C/M]@row)

the following screenshot shows the result:

Data Sheet

Formula Sheet

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

Try: =SUMIFS({Actual},{C/M},@cell=[C/M]@row)

• ✭✭✭✭✭
Options

Thanks, @Leibel S but that didn't work. Still returned a "0."

• ✭✭✭✭✭
Options

@Paul Newcome - any ideas with this one? I tried using the formula I have above but I changed the C/M from a number to text and it works perfectly. How do I get it to work with a number?

• ✭✭✭✭✭✭
Options

Try the below by adding the value cell should take care of any numbering issues.

=SUMIFS({Actual},{C/M},VALUE(@cell) = VALUE([C/M]@row))

• ✭✭✭✭✭✭
Options

Hope you are fine, please try the following formula:

=SUMIFS({Actual}, {C/M}, @cell = [C/M]@row)

the following screenshot shows the result:

Data Sheet

Formula Sheet

bassam.khalil2009@gmail.com

• ✭✭✭✭✭
Options

Thank you so much, @Bassam Khalil - that worked. Very cool. Thanks a ton.

• ✭✭✭✭✭✭
Options

You are welcome (Ton 😀) and I will be happy to help you any time.

bassam.khalil2009@gmail.com