My SUMIF formula is returning "0", what am I doing wrong?
I have a SUMF formula which is returning "$0" for the value I am trying to sum. My range and criterion is a number (employee id) is this possibly what is throwing it off? Will the formula read a number for for range and criterion?
Best Answer
-
Hey @MeyerL
May I offer it appears the employee ID is text in one sheet and a number value in the other. I say this noticing how [Employee ID] is left justified in one sheet and right justified in the other. It surprised me it made a difference but I verified the effect in my sheet. Building on the formula y'all have already worked out:
=SUMIF({CA}, CONTAINS([Employee ID]@row,@cell), {NSR})
Kelly
Answers
-
Hi @MeyerL
I hope you're well and safe!
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots and the formula? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Below is my formula
=SUMIF({CA}, [Employee ID]@row, {NSR})
My Range {CA} is referencing a separate sheet
My sum_range {NSR} is referencing the same separate sheet
-
Below is my formula
=SUMIF({CA}, [Employee ID]@row, {NSR})
My Range {CA} is referencing a separate sheet
My sum_range {NSR} is referencing the same separate sheet
-
Yes, the formula can read a number for for range and criterion.
Have you confirmed that the Employee ID is in the {CA} range?
-
@James Keuning yes. I just confirmed again that the Employee ID is in the range.
-
Thanks!
I'd be happy to take a quick look.
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots of the location of the formula? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå below are clips from my sheets. Let me know if this helps.
This is a screen shot of the formula and the sheet it is on, along with the Criterion listed in the same row.
Here is a screen shot of the range
This is a shot of the sum_range
-
your employee id is text, and the NSR is a number. probably best to make your NSR text. So create a column and use NSR@row+"" column formula
that will turn it to text
or you can make your employee id a number using VALUE
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@James Keuning and @Andrée Starå If I change the NSR column to text, will it still add up the NSR amounts?
-
No it won’t. Oops. So use VALUE against the employee id. That’s likely the shortest path. We can fins a way to reference the NSR number for summing if VALUE doesn’t work.
-
Hey @MeyerL
May I offer it appears the employee ID is text in one sheet and a number value in the other. I say this noticing how [Employee ID] is left justified in one sheet and right justified in the other. It surprised me it made a difference but I verified the effect in my sheet. Building on the formula y'all have already worked out:
=SUMIF({CA}, CONTAINS([Employee ID]@row,@cell), {NSR})
Kelly
-
@Kelly Moore That worked! Thank you!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!