vendredi 7 août 2015

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")
= #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")
= 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!

Aucun commentaire:

Enregistrer un commentaire