# Amend SUMIF formula based on value in dropdown?

#### bigpat

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

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.

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!

You're welcome. Glad to help

M.

Replies
4
Views
307
Replies
1
Views
441
Replies
4
Views
769
Replies
6
Views
238
Replies
0
Views
507

1,206,949
Messages
6,075,811
Members
446,158
Latest member
octagonalowl

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back