Loop through worksheets without using VBA

Question

Hi All,

I need to total certain cells based on a condition across all worksheets in the workbook.

The pseudocode would be

Foreach ws as Worksheet
    'total =SUMIF (A2:A5, "search term", B2:B5)
Next
MsgBox (total)

But I don't want to use a macro.

Any ideas?

Answer 1

You can do a 3D-reference (across multiple sheets), but only with SUM not with SUMIF.

You could put the SUMIF bit into a new column in each sheet, and then SUM the results of these with a 3D-reference.