SUMIFS Using 3 Distinct Criteria

[HGLIII]

Good Morning,

I was hoping someone might be able to help me out with the following question:

I have built a spreadsheet that generates data on a monthly basis for 100 distinct "blocks" (January through December, vertically oriented, 100 times; the example below features only 2 months for 1 of those 100 12-month "blocks"), with each month containing several identical categories (two of which are "Exp. X" and "Exp. Losses").

Column B features all of the listed categories and Columns C through V represent unique situations based on years, as such:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]JAN.[/TD]
[TD="align: center"]Category 1[/TD]
[TD="align: center"]1999 Data[/TD]
[TD="align: center"]2000 Data[/TD]
[TD="align: center"]2001 Data[/TD]
[TD="align: center"]2002 Data[/TD]
[TD="align: center"]2003 Data[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]JAN.[/TD]
[TD="align: center"]Category 2[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]JAN.[/TD]
[TD="align: center"]Exp. X[/TD]
[TD="align: center"]$500[/TD]
[TD="align: center"]$100[/TD]
[TD="align: center"]$300[/TD]
[TD="align: center"]$200[/TD]
[TD="align: center"]$400[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]JAN.[/TD]
[TD="align: center"]Exp. Losses[/TD]
[TD="align: center"]($50)[/TD]
[TD="align: center"]$0[/TD]
[TD="align: center"]($100)[/TD]
[TD="align: center"]$0[/TD]
[TD="align: center"]($200)[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]JAN.[/TD]
[TD="align: center"]Category 5[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]JAN.[/TD]
[TD="align: center"]Category 6[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]JAN.[/TD]
[TD="align: center"]Category 7[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]FEB.[/TD]
[TD="align: center"]Category 1
[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]FEB.[/TD]
[TD="align: center"]Category 2
[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]FEB.[/TD]
[TD="align: center"]Exp. X
[/TD]
[TD="align: center"]$200[/TD]
[TD="align: center"]$300[/TD]
[TD="align: center"]$100[/TD]
[TD="align: center"]$200[/TD]
[TD="align: center"]$500[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]FEB.[/TD]
[TD="align: center"]Exp. Losses
[/TD]
[TD="align: center"]$0[/TD]
[TD="align: center"]$0[/TD]
[TD="align: center"]($25)[/TD]
[TD="align: center"]$0[/TD]
[TD="align: center"]$0[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]FEB.[/TD]
[TD="align: center"]Category 5
[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]FEB.[/TD]
[TD="align: center"]Category 6
[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]FEB.[/TD]
[TD="align: center"]Category 7
[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]"'[/TD]
[TD="align: center"]""[/TD]
[TD="align: center"]""[/TD]
[/TR]
</tbody>[/TABLE]

Would it be possible to SUM all of the Exp. X numbers in Column C (which would encompass both the January and February results of course), but only those that also feature a loss in Exp. Losses?

Meaning, something along the lines of SUMIFS(B1:B15,"*Exp. X*",C1:C15) but then only if AND(B1:B15,"*Exp. Losses*",C1:C15,"<0"). I am trying to pair the SUM of the Exp. X with only the cells that also feature a negative number in the Exp. Losses cell.

The actual results for Column C would equal $500, because the $200 Exp. X in the month of February does not feature a loss in the Exp. Loss category.

Any help with crafting the formula would be greatly appreciated!

Many thanks,

HGL

 

[dms37]

My real answer is you'd be better with a helper column you just sum but if it needs to be a formula in one cell this works when confirmed with Ctrl+Shift+Enter

=SUM(IF(B1:B14=$B$3,IF(B2:B15=$B$4,IF(C2:C15<0,C1:C14,0),0),0))

 

[HGLIII]

Thanks very much for the reply - if you have a second, would you mind explaining the rationale behind the formula? For example, what is the purpose of using B1:B14 instead of B15 to encompass the entire range, as well as then using B2/C2:B15/C15 followed by C1:C14? Additionally, why does B1:B14 = $B$3 instead of $B$4, which is the targeted reference cell?

I am conveying this formula to a pre-existing sheet, so understanding the logic behind it would be helpful if you don't mind explaining.

Thanks!

 

[dms37]

There are only 14 rows, I ignored #1 being the line with A, B, C etc in

 

[HGLIII]

Understood, thank you - for some reason the formula isn't working for me.

I made sure to create an array formula with CTRL+SHFT+ENTER, but still no luck.

Do you think it would be easier to explain the logic behind a nested IF statement (I suppose that is what we are doing with this)?

To reiterate, just trying to create a formula that encompasses the following: SUM all of the cells in Column C that correspond to the cells in Column B titled "Exp. X", if and only if the cells in Column C that correspond with the cells in Column B titled "Exp. Losses*" are less than $0 (negative).

 

[dms37]

The nested ifs are because an array formula won’t allow an AND function in the IF. So they’re just if the first criteria is met, move on to the second and if that is met then check the third. If all are met return the corresponding result from the range otherwise return zero and then sum all the answers.

It definitely works as I tried it first before posting, only thing that I had to tweak is the $ signs in the money made my Excel think they were text but that’s just because I copied it from your post.

 

[HGLIII]

Excellent explanation, thanks dms - here is my formula:

{=SUM(IF($B$2:$B$16901="*Exp. X*",IF($B$2:$B$16901="*Exp. Losses*",IF(C2:C16901<0,C2:C16901,0),0),0))}

Any thoughts as to what I might be doing wrong here? I think there might be something going on with the textual component ("*Exp. X*" and "*Exp. Losses*") - perhaps you can't use "=" followed by the "* *"?.

 

[HGLIII]

* The only result I can achieve is "$0".

 

[dms37]

The ranges for checking exp losses and the <0 need to be 1 row lower as the data is underneath the exp x and the vales being summed. The middle 2 ranges in the formula need to be from rows 3 to 16902

 

[HGLIII]

Really appreciate the help - thanks again!