SUMIF with criteria as a number

Jennifer Lenander

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?

Bassam Khalil

Hi @Jennifer Lenander

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

Leibel S

@Jennifer Langan

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

Jennifer Lenander

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

Jennifer Lenander

@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?

Leibel S

@Jennifer Lenander

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))

Bassam Khalil

Hi @Jennifer Lenander

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

Jennifer Lenander

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

Bassam Khalil

@Jennifer Lenander

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