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
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!