SUMIF with criteria as a number
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?
Best Answer

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
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers

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

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

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

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

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
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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

You are welcome (Ton 😀) and I will be happy to help you any time.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!