Start:Finish with If formula

[Bongard]

Hi, I am trying to conditionally sum across multiple tabs. I have a
workbook with about 15 worksheets and I need to sum the amount of
students on each worksheet (cells B9:B27 on every tab) each time for
the respective subject (C:9:C27 on every tab) on my Summary worksheet.
I tried the following formula but am getting a #REF! error

{=SUM(IF(Start:Finish!$C$9:$C$27=A11,Start:Finish!$B$9:$B$27,""))}

This says if C9:C27 = A11 (the subject on my summary worksheet) then
sum B9:B27 and do so on every worksheet from Start:Finish, or at least
thats what I am trying to get it to do. I know this can't be that
difficult but would someone please point me in the right direction?

I would greatly appreciate it!

Thanks in advance,
Brian

 

[Guest]

Maybe I'm not understanding you, but why don't you summarize each sheet's
data in its own sheet, and then sum the summaries in a separate sheet?

Dave

 

[Bongard]

I thought of that as well, but each worksheet is a template that I
don't want to disturb, it would be ideal if the totals could just be
done on the summary tab.

Thanks for your input though Dave, any other ideas on how to get a
formula like the original to work?

-Brian

 

[Guest]

Assuming you have three sheets "Sheet1:Sheet3", and a "Summary" sheet

In Summary sheet
=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!C9:C27"),Summary!A11,INDIRECT("Sheet"&{1,2,3}&"!B9:B27")))

 

[Bongard]

I have about 15 sheets, and they are all different people's names for
exmaple "Brrent, Amber, Lisa, Brad" are all different sheets. How then
would the formula look?

 

[Guest]

First create a list range from A1 to A15 and call it "MySheets" no quote
A1: Brent
A2: Amber
A3: Lisa
and so on...

=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!C9:C27"),A11,INDIRECT("'"&MySheets&"'!B9:B27")))

 

[Bongard]

I copy pasted this exact thing into my cells and it wouldn't work

{=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!
C9:C27"),A11,INDIRECT("'"&MySheets&"'!B9:B27"))}

I created the list named my sheets just the way you said, but no go.
I don't really know what I am doing wrong.

 

[Guest]

My formula doesn't include the curly brackets { }

 

[Bongard]

The curly brackets just make it an array. I tried entering the formula
as an array and normally and still it doesn't work. Anyone have any
ideas on how to use my orignal conditional sum from start:finish?