Struggling with indirect sumifs

**Mgahan** · 02-08-2018, 07:03 PM

Hey Guys,

Im trying to get the sumifs function to work across a number of tabs based off 3 criteria but it keeps coming back with an error, plz help.

Formula is below:

=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&Store_Table[Store]&"'!C:C"),INDIRECT("'"&Store_Table[Store]&"'!$A$1"),$Y$5,INDIRECT("'"&Store_Table[Store]&"'!$1:$1"),$Y$10,INDIRECT("'"&Store_Table[Store]&"'!$A:$A"),$D$15)),"ERROR")

Im trying to sum a column if the store name matches (Which is the first criteria), if the year matches (criteria 2) and if the account name matches (criteria 3).

**ben_hensel** · 02-08-2018, 07:35 PM

Looks like you've got a reference to A:1 when you meant a reference to A:A in the second term of the SUMIFS there (the first Criteria Range).

And the Fourth input (the second Criteria Range) is being given a row when the other ranges are columns -- and since you're just telling it to work on the WHOLE ENTIRE RANGE instead of a selection, the fact that there are more rows than columns might also be a problem -- it would throw the #VALUE error.

Also you've got it wrapped with a SUMPRODUCT that isn't really doing anything.

Thus what you probably want is:

Please Login or Register  to view this content.

Note that the B:B might not be the right range.

Also you're referencing A:A in two ranges, so if Y5 <> D15, then the function will always return zero.