# My SUMIF formula is returning "0", what am I doing wrong?

Options
✭✭✭

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭
Options

Yes, the formula can read a number for for range and criterion.

Have you confirmed that the Employee ID is in the {CA} range?

• ✭✭✭
Options

@James Keuning yes. I just confirmed again that the Employee ID is in the range.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭
Options

@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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

I agree with James.

Was that the issue?

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.

• ✭✭✭
Options

@James Keuning and @Andrée Starå If I change the NSR column to text, will it still add up the NSR amounts?

• ✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

@Kelly Moore That worked! Thank you!!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!