sheetlist sumifs with multiple criteria - shortened?

[Long Nose]

Is there a way I can shorten this formula.

Works - This adds "US", "VI", "PR", and excludes "CA"
SUMPRODUCT(SUMIFS(INDIRECT("'"&TACO_LIST&"'!$d:$d"),INDIRECT("'"&TACO_LIST&"'!$b:$b"),"US",INDIRECT("'"&TACO_LIST&"'!$c:$c"),$B4))+SUMPRODUCT(SUMIFS(INDIRECT("'"&TACO_LIST&"'!$d:$d"),INDIRECT("'"&TACO_LIST&"'!$b:$b"),"VI",INDIRECT("'"&TACO_LIST&"'!$c:$c"),$B4))+SUMPRODUCT(SUMIFS(INDIRECT("'"&TACO_LIST&"'!$d:$d"),INDIRECT("'"&TACO_LIST&"'!$b:$b"),"PR",INDIRECT("'"&TACO_LIST&"'!$c:$c"),$B4))

Also works - One option was add all and subtract "CA"
=SUMPRODUCT(SUMIFS(INDIRECT("'"&TACO_LIST&"'!$d:$d"),INDIRECT("'"&TACO_LIST&"'!$c:$c"),$B4))-SUMPRODUCT(SUMIFS(INDIRECT("'"&TACO_LIST&"'!$d:$d"),INDIRECT("'"&TACO_LIST&"'!$b:$b"),"CA",INDIRECT("'"&TACO_LIST&"'!$c:$c"),$B4))

Doesn't work - What I wanted to do is create a named range CNTY = ("US", "VI", "PR") and use that only. Like this, but it doesn't work.
SUMPRODUCT(SUMIFS(INDIRECT("'"&TACO_LIST&"'!$d:$d"),INDIRECT("'"&TACO_LIST&"'!$b:$b"),CNTY,INDIRECT("'"&TACO_LIST&"'!$c:$c"),$B4))

Is there a trick I can get CNTY range to work?

 

[Domenic]

If TACO_LIST refers to a vertical range of cells, CNTY needs to refer to a horizontal range of cells. Or, if TACO_LIST refers to a horizontal range of cells, CNTY needs to refer to a vertical range of cells.

So, for example, TACO_LIST could refer to the range A2:A10, and CNTY could refer to the range D2:F2. As you can see the former is a vertical range of cells, whereas the latter is a horizontal range of cells. So this would work.

Although, if both ranges are either vertical or horizontal you can use the TRANPOSE function to flip one or the other. So, let's say that TACO_LIST refers to A2:A10, and CNTY refers to D2:D4. As you can see both are vertical ranges. So you could do like this...

=SUMPRODUCT(SUMIFS(INDIRECT("'"&TACO_LIST&"'!$d:$d"),INDIRECT("'"&TACO_LIST&"'!$b:$b"),TRANSPOSE(CNTY),INDIRECT("'"&TACO_LIST&"'!$c:$c"),$B4))

Hope this helps!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Long Nose]

Microsoft® Excel® for Microsoft 365 MSO (Version 2306 Build 16.0.16529.20226) 64-bit

Transpose was the issue. Both my sheetlist and named range were vertical. I just invoked your transpose function on one range and boom! -works.

Thank you so much, Domenic!

 

[Domenic]

You're very welcome, glad to help.

Cheers!