SUMIFS returning NIL on multiple conditions in one column

[mysticmoron109]

For some reason when I do SUMIFS it is returning a NIL balance when I am confident the data is right.

For example, Column D has 5 variables in it: Red, White, Blue, Black, Gray which repeat themselves a number of times. Column E has values in it.

D E
Red 5
Black 6
White 3
Red 2
Blue 3
Black 4
White 3
Gray 2
Blue 1
Red 9
Gray 12
Blue 3
Black 2
White 3
White 4
Gray 10

If i Do a SUMIFS(E:E, D:D, "red", D:D, "blue", D:D, "white")

this will return NIL. But if I do a SUMIFS on say red only it pulls the number. Any input please?

Thanks!

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[daffodil11]

There are no circumstances where D contains all of those conditions simultaneously.

Instead, try =SUM(SUMIF(E:E,{"red","blue","white"},D:D))

[Sam Capricci]

You need to do =sumif()+sumif()+sumif for that (hard to do from an iPad but hopefully you get the picture).

Edit
: wow in the time it took me to type that you got two other replies! iPads just aren't efficient.

[mysticmoron109]

thank you guys very much! will give it a shot .

for my understanding then, SUMIFS must be treated like an array if looking for multiple coniditions in a single column?

[TMS]

If you use the Evaluate formula tool on the Formula ribbon, you will see that the formula evaluates each of the SUMIFS and the SUM adds the results. So, yes, an array of individual SUMIFS.

Regards, TMS



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[TMS]

@daffodil11: think you're adding the wrong column.

@Sambo: there is another way See above.

Regards, TMS

[MarvinP]

Hi mysticmoron,

I'm too lazy to learn these formulas if I think a Pivot Table (that doesn't need any formulas) can give me the answer I want. See if the attached, using a Pivot Table will be a better way of getting your answer.

[daffodil11]

Yeah yeah, I made my own example and then tried a freehand conversion.

@Sambo: That was my first post too. And then I remembered one of the old guys showed me how to feed a string of variables.

[mysticmoron109]

Superb, thank you very much.

unfortunately this is a part of a much larger project so it needs to be formulated. the PT cant help in this instance but thanks much for your input

CHeers!

[TMS]

Old guys?

[Sam Capricci]

Well I admit to being an "old guy" but offhand I didn't even consider a string as a solution. Guess it's that old guy thing.

[daffodil11]

:coughthatsninetyninepercentoftheforumpopulationcough:

[Tony Valko]

How old is old?

I'm 50 something! Do I "qualify" ?

Use cells to hold the criteria:

A1 = red
A2 = white
A3 = blue

=SUMPRODUCT(SUMIF(D:D,A1:A3,E:E))