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.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!