Assistance with sumifs across with multiple tabs

[BLIMA]

Hello everyone,

I'm trying to put together a sumifs condition working with (2) criteria across (12) tabs, but I'm getting a #REF!.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&M1:M2&"'!$S$2:$S$67"),INDIRECT("'"&M1:M2&"'!$F$2:$F$67"),B$1,INDIRECT("'"&M1:M2&"'!$C$2:$C$67"),$A2))

In essence, M1:M2 would be associated to my months, so it'd actually be M1:M12, "$S$2:$S$67" is my range #01, !$F$2:$F$67 is my range #02 (consistently repeats across every other worksheet), B$1 is my criteria #01, and $A2 is my criteria #02 associated to my range #03, $C$2:$C$67.

Thoughts?

 

[jasonb75]

The #REF! error implies that 1 or more of the sheets listed in M1:M2 (or M1:M12) does not exist.

As you say that they refer to months, it would be worth checking the format. If the range M1:M12 contians proper dates then the formula will be looking for date serial numbers on the sheet names rather than the actual dates, meaning if M1 contains "Jan 2021" as a valid date then the formula will be looking for a sheet named '44197' which is the date serial number for Jan 1st 2021.

If this is the case then you would need to incorporate the TEXT function to convert the dates to text strings so that they match the sheet names, either in the cells M1:M12, in another range of 12 cells or as an array in the sumproduct formula.

 

[BLIMA]

The #REF! error implies that 1 or more of the sheets listed in M1:M2 (or M1:M12) does not exist.

As you say that they refer to months, it would be worth checking the format. If the range M1:M12 contians proper dates then the formula will be looking for date serial numbers on the sheet names rather than the actual dates, meaning if M1 contains "Jan 2021" as a valid date then the formula will be looking for a sheet named '44197' which is the date serial number for Jan 1st 2021.

If this is the case then you would need to incorporate the TEXT function to convert the dates to text strings so that they match the sheet names, either in the cells M1:M12, in another range of 12 cells or as an array in the sumproduct formula.

Thanks Jason,

So if I name my tabs using numbers instead it might work? i.e. instead of M1, I use 01.

 

[jasonb75]

So you're saying M1 and M2 are tab names, not cell references?

I read it that you had the tab names in cells M1, M2, M3, etc. Which should have worked. If those are tab names then you would need to use array constants.

INDIRECT("'"&{"M1","M2,"M3","M4","M5"}&"'!$S$2:$S$67")

Which should work in theory, but I've never actually tried it that way.