How do I SUMIF if column of valunes also contains text please?

[StuLeslie]

Hi folks

Long time user. first time caller.

I have a vast spreadsheet measuring 26000+ rows and columns out to AL and everything has been working great for me for many years. But I was asked the other how may X co-worker Y had achieved.
I used my SUMIF formula and it kept returning zero even though I know it should return a value.
=SUMIF(E3:G43,"SL",E3:E43)

And I think my issue is that some of my values aren't know so then I enter "unknown" in that row. And some of the X unknowns correspond to the Y co-worker. And I suspect but can't prove that is where my SUMIF is stumbling.
I've formatted the column with values to be "Numbers" and that makes no difference. Unfortunately for me, I need the unknowns to be in that column.

Therefore I suspect I need a SUMIF but with extra conditions included, yes?

Thank you very much in advance for any new SUMIF tricks I am taught.




Excel Example for SUMIF formula issue.xlsx

[AliGW]

Welcome to the forum.

Is it Excel 2016 that you are using?

[habib100]

Welcome to the forum.

Is it Excel 2016 that you are using?

hi
No, its 2024

[CARACALLA]

I3=sumproduct((f3:g26000="sl")*(n(+e3:e26000)))

[TMS]

Another option:

Please Login or Register  to view this content.

[StuLeslie]

My hugest aplogies AliGW, yes, Office16
Sorry

[StuLeslie]

Thank you very very much Caracalla.
I would never have found what I was looking for continuing with SUMIF when what I actually needed was SUMPRODUCT.
I truly appreciate you.

Ciao
Stu

[TMS]

Thanks for the rep.

Yes, I was hoping that you might have upgraded ... and habib100 seemed to be running interference. Not sure why?

Glad you have a solution.

[daddylonglegs]

And I think my issue is that some of my values aren't know so then I enter "unknown" in that row. And some of the X unknowns correspond to the Y co-worker. And I suspect but can't prove that is where my SUMIF is stumbling.

SUMIF can cope fine with text in the sum column, it just ignores it....but if you are trying to sum column E when F or G is = "SL" then that's not going to work because SUMIF works on a "one to one" basis, i.e. the sum range and the criteria range need to be the same size and shape

When you use this formula

=SUMIF(E3:G43,"SL",E3:E43)

then excel implicitly changes the sum range to be the same size as the criteria range, so your formula is effectively this

=SUMIF(E3:G43,"SL",E3:G43)

That will always return zero whatever your data because no cell can be both = "SL" and a number

This formula works to check column F for "SL" and sum column E

=SUMIF(F3:F43,"SL",E3:E43)

but if you need to check both columns F and G for "SL" you could use two SUMIF functions like this

=SUMIF(F3:F43,"SL",E3:E43)+SUMIF(G3:G43,"SL",E3:E43)

....although that will double count if "SL" is shown twice in one row

CARACALLA's suggested formula will also double count so gives the same result as my two SUMIFs above. If you only want to sum any row once, even if there are two "SL"s then this version will do that

=SUMPRODUCT(((F3:F43="SL")+(G3:G43="SL")>0)+0,E3:E43)

and so will this "array formula" version

=SUM(IF((F3:F43="SL")+(G3:G43="SL"),E3:E43))

Notice that SUM and SUMPRODUCT can also ignore text as long as you don't try to do any mathematical operations on the text values within those functions

[AliGW]

hi
No, its 2024

I wasn't asking you. Don't reply to questions that clearly are not for you, as it's confusing. You have added nothing helpful to this thread - don't do this again.