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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!