Help with SUMPRODUCT on multiple columns

[bpigott]

Hello all!

I know this has to be simpler than I'm making it, but my brain is drawing a blank right now!

I've used this SUMPRODUCT formula before to count the total number of occurrences of a certain piece of data across multiple sheets within a workbook, but I'm only able to pull these together one column at a time. As you'll see in the attached sheet, I'm counting the total number of reviews done for each agent in our department month-over-month, but want to avoid an extremely lengthy formula to add them all.

=SUMPRODUCT(SUMIF(INDIRECT("'"&months&"'!"&"B:B"),B2,INDIRECT("'"&months&"'!"&"C:AG")))

Months of course, is the named range for each sheet and columns C:AG are the cells I'm trying to total up for each agent.

There's an easier way to do this correct?

[wk9128]

TRY C2 cell array formula
{=SUM(SUMIF(INDIRECT(months&"!B:B"),B2,OFFSET(INDIRECT(months&"!C:C"),,TRANSPOSE(ROW($1:$31)-1),,)))}

if you want manager and employee criteria pls try to use sumifs formular
{=SUM(SUMIFS(OFFSET(INDIRECT("'"&months&"'!C:C"),,TRANSPOSE(ROW($1:$31)-1),),INDIRECT("'"&months&"'!A:A"),$A2,INDIRECT("'"&months&"'!B:B"),$B2))}

[bpigott]

Awesome, that does the trick! Much appreciated!