Sum with IF

[Jane]

Ok, so i have 1 thru 999 worksheets and i need a sum of IF formulas. But
SUMIF doesn't do what i need it to... I have IF('1'!B72,'1'!E2,0) what i
want is =SUM(IF('1'!B72,'1'!E2,0):IF('999'!B72,'999'!E2,0)) but it won't
let me do that. Do you understand my question?

Thanks!

 

[T. Valko]

Do you understand my question?

Not really, but here's my best guess:

=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT("1:999"))&"'!B72"),"<"&1E+307,INDIRECT("'"&ROW(INDIRECT("1:999"))&"'!E72")))

 

[Jane]

Im going to try what you have written there, but i don't understand any of
it, sorry!
But what I need is to find a way to do
IF('1'!B72,'1'!E2,0)+IF('2'!B72,'2'!E2,0)+...+('999'!B72,'999'!E2,0) without
having to type that in for every sheet. So i need a sum of IF functions.
make more sense? Thanks, im going to try that formula you gave me, but if i
would have to modify it i wouldn't know what to do... Thanks for you help
either way!

 

[Jane]

yeah, it didn't work

 

[T. Valko]

IF('1'!B72,'1'!E2,0)

The logic of your formula is:

If '1'!B72 is *any* number other than 0 or, if it's a boolean TRUE, then
return the value of '1'!E2, otherwise, return 0.

So, the formula I suggested is doing a SUMIF across all the sheets like
this: (this syntax won't work)

=SUMIF(1:999!B72,"any number",1:999!E72)

 

[Fred Smith]

So what we hear you saying is you don't want any more help. If that wasn't
your intention, then post back with more information, like, what didn't
work -- error message? wrong result? Also, the easiest way to more clearly
explain the solution you are looking for is to give examples.

Regards,
Fred.

 

[Herbert Seidenberg]

Simplified for maintainability.
http://www.freefilehosting.net/download/3bk34

 

[Jane]

Hi,

Ok, I do still need more help with this. I will tell you exactly what I have:

Ok, so I have multiple spreadsheets which I will have to continually add to
(hence the 999'th worksheet). Anyways, I have a correlation spreadsheet at
the end.

On each spreadsheet I have a chart in which a '1' means they fulfill that
'requirement'. For example, a 1 beside RN = they are a registered nurse and
not a diploma nurse, but a 1 beside Diploma nurse = they are NOT a registered
nurse, but a diploma nurse. On each spreadsheet I also have a chart that is
an evaluation, in which they put a 4 for they liked the course and a 3 for
workload, etc. On the correlation spreadsheet, I want to have - if the
evalation was written by an RN and they scored a 4 for how they liked the
course for it to go on the correlation chart. But then i want a SUM of all
the RN responses for that evaluation question. I then have a separate chart
for the SUM of all the diploma nurse evaluation questions.

Does this make sense?

How much did you like the course: 4
Workload was manageable: 3
RN: 1
Diploma nurse: (blank)

Then on another sheet, might have a dipoma nurse.

So the IF is: if RN = true, then the value for the cell on the correlation
sheet for "How much did you like the course" is 4 and 0 if RN = false. Then
I need to sum that across all the spreadsheets.

Right now i will have to add into the formula each time I get another
'evaluation' spreadsheet that I want to have data on the correlation sheet.
For example:
=SUM(IF('1'!B72,'1'!E2,0)+IF('2'!B72,'2'!E2,0)+...)

Also, this formula just gives me a whole bunch of ## signs.
=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW(INDIRECT("1:999"))&"'!B72"),"<"&1E+307,INDIRECT("'"&ROW(INDIRECT("1:999"))&"'!E72")))


=SUMIF('1:999'!B72,1,'1:999'!E72) - this also gives me the ##

I hope this helps explain my predicament!!! Thanks so much for helping!
-jane

 

[Jane]

i've tried to figure out how to get the workbook on that website but i don't
know how! sorry!!

-jane