SUMIFS Using 3 Distinct Criteria

HGLIII

New Member
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:

1ABCDEFG
2JAN.Category 11999 Data2000 Data2001 Data2002 Data2003 Data
3JAN.Category 2""""""""""
4JAN.Exp. X$500$100$300$200$400
5JAN.Exp. Losses($50)$0($100)$0($200)
6JAN.Category 5""""""""""
7JAN.Category 6""""""""""
8JAN.Category 7""""""""""
9FEB.Category 1
""""""""""
10FEB.Category 2
""""""""""
11FEB.Exp. X
$200$300$100$200$500
12FEB.Exp. Losses
$0$0($25)$0$0
13FEB.Category 5
""""""""""
14FEB.Category 6
""""""""""
15FEB.Category 7
"""""'""""

<tbody>
</tbody>

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

Board Regular
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

New Member
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!
 

HGLIII

New Member
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

Board Regular
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

New Member
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 "* *"?.
 

dms37

Board Regular
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
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top