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

MeyerL

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?

Kelly Moore

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

Andrée Starå

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!

MeyerL

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

MeyerL

@Andrée Starå

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

James Keuning

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

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

MeyerL

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

Andrée Starå

@MeyerL

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)

MeyerL

@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

James Keuning

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

Andrée Starå

@MeyerL

I agree with James.

Was that the issue?

MeyerL

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

James Keuning

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.

Kelly Moore

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

MeyerL

@Kelly Moore That worked! Thank you!!