SUMIFS With Multiple Criteria in Same Columns

[TryingMyBest1]

Hello,

I have a formula in front of me that I'm completely stuck on. I need to pull a sum from a raw data sheet in another tab and I need it to only pull #'s with specific criteria from the same columns.

Here is what I have so far...

=SUMPRODUCT(SUMIFS(INDEX(licensefeedetail,0,(MATCH(year,licensefeedetailheaders,0))),INDEX(licensefeedetail,0,(MATCH("DMA",licensefeedetailheaders,0))),B5,INDEX(licensefeedetail,0,(MATCH("Package Name",licensefeedetailheaders,0))),A5,INDEX(licensefeedetail,0,(MATCH("Sub Type",licensefeedetailheaders,0))),{"BOTH","RETAIL"}))


As this formula stands everything works fine. The problem I'm having is the B5 cell reference that I bolded. That cell is a dropdown list of different cities. I need to have this formula pull data that's tied to whichever city is in that cell reference AND pull data from the same column with cells that contain "All".

I tried using {B5,"All"} but arrays won't recognize cell references. And even if I just try to put {"Atlanta","All"} that won't work because I think the formula is getting caught up with the "BOTH"/"RETAIL" array.

Is there anyway I can pull #'s that have the criteria "BOTH" and "RETAIL" in one column while pulling #'s tied to a city from a cell reference and all cells that contain "All" from another, separate column?

Thank you

 

[pgc01]

Hi
Welcome to the board

Try instead of B5

CHOOSE({1;2},B5,"All")

 

[TryingMyBest1]

Hi
Welcome to the board

Try instead of B5

CHOOSE({1;2},B5,"All")

Holy cow you are a lifesaver my friend! Works like a charm.

Thank you so much

 

[pgc01]

You're welcome. Thanks for the feedback.