SUM Across Multiple Sheets with Reference Cell

[johnny923]

Trying to sum across multiple sheets with the ability to include (Y) or exclude (N) certain data sets. I think it's a SUMIF but can't quite get the formula to work so that I can reference the Y or N cells.

I tried searching for a solution but didn't see one.

Thank you!

 

[Z51]

Formula below is based on information from this page: How to Use the SUMIF Function Across Multiple Sheets | Excelchat

As long as your tables are laid out the same across all sheets, this formula in Summary!B3 (filled across and down) may work. You should also create the named range (a list of your worksheet names) in a range of cells on one of the sheets (or in a new sheet strictly for that purpose). In my formula below, I named it Regions, same as in the website's example.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Regions&"'!"&CHAR(COLUMN()+64)&"$1"),"Y",INDIRECT("'"&Regions&"'!"&CHAR(COLUMN()+64)&ROW(B3))))

 

[johnny923]

Thanks! Much appreciated.

 

[Z51]

Sure thing.

Note the 'CHAR(COLUMN()+64) references are to convert the column # into a letter. CHAR(65) = A, CHAR(90) = Z, so if you enter CHAR(COLUMN()+64) into B3, it calculates as
CHAR(COLUMN(B3)+64)
CHAR(2+64)
CHAR(66) = B