Amend SUMIF formula based on value in dropdown?

[bigpat]

Hi,

I have a formula as follows, which works fine.

SUMIF('Overview report'!$AK:$AK,"Yes",'Overview report'!P:P)

However, I also have a dropdown in cell O10 with a choice of seven values, and column AK in that formula is appropriate only for the first dropdown option.

So if I have chosen the second dropdown option, the formula needs to be SUMIF('Overview report'!$AL:$AL,"Yes",'Overview report'!P:P)
and for the third option, it needs to be SUMIF('Overview report'!$AM:$AM,"Yes",'Overview report'!P:P)
etc.

Do I need to construct a really long nested IF statement? e.g. =IF(O10="Red", [insert formula 1], IF(O10= "Blue", [insert formula 2].... )))))))

Or is there a smarter approach?

 

[Marcelo Branco]

Say the validation list of 7 values used in O10 is named MyList.
So, maybe something like this...
=SUMIF(INDEX('Overview report'!AK:AQ,0,MATCH(O10,MyList,0)),"Yes",'Overview report'!P:P)

Hope this helps

M.

 

[bigpat]

This does help. You nailed it!

I keep hearing about using Index and Match together but I've never taken the time to understand them properly. I'd really better start, because this is so much better than what I was going to attempt.

Thank you!

 

[Marcelo Branco]

You're welcome. Glad to help

M.