Sumproduct-Sumif-Indirect Formula Problem

instead of going for this big construction
you can use below in c5 as your sheets are identical
=SUM(Location_A:Location_C!C5)
drag down and across


Click C5 type
=Sum(
then select first sheet hold shift and select last sheet then Click C5 then ) and hit ok
drag down and across

Try

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$5:$A$7&"'!B5:B10"),LEFT($B5) & "~" &RIGHT($B5,LEN($B5)-1),INDIRECT("'"&$A$5:$A$7&"'!C5:C10")))

the "~" negates the "*" as wildcard.


FYI: I found the answer by "Googling" so you can often get the answer to queries like this from the Internet.

Array formula are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

@ John with due respect, I think totals are missing
@PJH try below in C5 and then drag down and across, array entered

Formula:

Please Login or Register  to view this content.

Red part is rows from 5 to 10 and 2 represent column B so it becomes B5,B6,B7,B8,B9 and so on

Second red part Column(C1) represents 3 then in next cell it become Column(D1) means 4 and so on

Updated formula ..

=IF(ISNUMBER(SEARCH("~*",$B5)),SUMPRODUCT(SUMIF(INDIRECT("'"&$A$5:$A$7&"'!B5:B10"),LEFT($B5)&"~"&RIGHT($B5,LEN($B5)-1),INDIRECT("'"&$A$5:$A$7&"'!C5:C10"))),SUMPRODUCT(SUMIF(INDIRECT("'"&$A$5:$A$7&"'!B5:B10"),$B5,INDIRECT("'"&$A$5:$A$7&"'!C5:C10"))))

non-array

You are welcome PJH and thanks for feedback