sumif across worksheets

[Besh]

My understanding is that I cannot do sumif across multiple worksheets. As such, what should I do to achieve the below?

Across 10 identical worksheets, I want to sum e54 if B2 = "TB"

Thanks, Besh

 

[TiaXL]

do it in VBA cycle through each worksheet checking if your check is true and if so store the value of e54 and then sum at the end ?

 

[T. Valko]

My understanding is that I cannot do sumif across multiple worksheets. As such, what should I do to achieve the below?

Across 10 identical worksheets, I want to sum e54 if B2 = "TB"

Thanks, Besh

Do the sheet names follow some sort of sequential naming pattern?

Like the default sheet names are Sheet1, Sheet2, Sheet3, etc.

Or, are the sheet names random? Boston, Atlanta, Pittsburgh, Orlando, etc.

 

[Besh]

Yes, the names have a sequential pattern. It's "Case 1", "Case 2" and so on through to "Case 10". They are to tabulated on a sheet called "Cover".

 

[T. Valko]

Yes, the names have a sequential pattern. It's "Case 1", "Case 2" and so on through to "Case 10". They are to tabulated on a sheet called "Cover".

Try this...

=SUMPRODUCT(SUMIF(INDIRECT("'Case "&{1,2,3,4,5,6,7,8,9,10}&"'!B2"),"TB",INDIRECT("'Case "&{1,2,3,4,5,6,7,8,9,10}&"'!E54")))

You can use a defined name to replace the array constant{1,2,3,4,5,6,7,8,9,10}

Name: Sheets
Refers to: ={1,2,3,4,5,6,7,8,9,10}

Then the formula becomes:

=SUMPRODUCT(SUMIF(INDIRECT("'Case "&Sheets&"'!B2"),"TB",INDIRECT("'Case "&Sheets&"'!E54")))

 

[Besh]

nice, works. you're the man. thanks.

thanks to you as well tiaxl, i never got fully through your suggestion b/c i've never used vba - i'll read up on it more once this project is over.

best regards, besh

 

[T. Valko]

nice, works. you're the man. thanks.

thanks to you as well tiaxl, i never got fully through your suggestion b/c i've never used vba - i'll read up on it more once this project is over.

best regards, besh

You're welcome. Thanks for the feedback!