CODEWITHSUNDEEP
excelif-statementexcel-formulasumifs
Kelley Hamrick
Kelley Hamrick

Reputation: 197

Linking IF statement (criteria in single column) with SUMIFS (criteria spans range of columns)

I've run into an issue with trying to sumifs across a range of columns. The way the data is structured, I have a range of countries (column B-H) and a row of associated numbers (column I-O). I'd like to sum the volumes with associated countries (ex: XX for Afghanistan) - however, there's a catch. I only want to sum across those columns if another criteria (denoted by "yes use this") appears in column FI. I'm having a hard time getting all of these pieces to work together -- any ideas??

Attempt 1:

=SUMIFS(I5:O1000,B5:H1000,"Afghanistan",FI5:FI1000,"yes use this")

Result : = #VALUE error

I'm assuming this is because the ranges don't match (B5:H1000 spans 7 columns, FI5:FI1000 spans 1).

Attempt 2:

=SUMIFS(I5:I1000,B5:B1000,"Afghanistan",FI5:FI1000,"Yes use this")

Result: the correct number

The above assumption was correct, but then this doesn't solve my problem. I don't want to have to copy and repeat the formula for each column.

Attempt 3:

=IF(FI5:FI1000="Yes use this", SUMIFS(I5:I1000,B5:B1000,"Afghanistan"),"FAILED")

Instead of pulling back the correct number, the formula spits out FAILED - which is incorrect.

Any thoughts are appreciated, thanks!

Upvotes: 0

Views: 384

Answers (1)

xidgel
xidgel

Reputation: 3145

You can try:

=SUM(IF(B5:H1000="Afghanistan",IF(FI5:FI1000="yes use this",I5:O1000,0),0))

entered as an array formula (CTRL-ENTER)

Upvotes: 0

Related Questions