About IF and summing IF over multiple spreadsheets

[Jane]

Hi,

I asked this question last week but i think it got lost and i wasn't
descriptive enough with my question, so here we go again:
=SUM(IF('1'!B72,'1'!E2,0):IF('999'!B72,'999'!E2,0)) that is what i want it
to do, but all it comes up with is ## when i hit enter. Essentially, i have
IF formulas that work great but instead of writing each sheet in (because I
will be adding more sheets in as time goes by) is there another way of doing
it?
=SUM(IF('1'!B72,'1'!E2,0)+IF('2'!B72,'2'!E2,0)+...+IF('999'!B72,'999'!E2,0))

So essentially, how do I add IF results from across spreadsheets? I have a
'sum' spreadsheet that I want to have IF functions 'summed' up on. Does this
make sense?

For example, on each worksheet, I have something similar to this:

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

I need to separate (on the Sum sheet) between answers from RNs and answers
from Diploma. So my IF is whether or not RN = true (has a 1) and then the
number for the first question is the number if RN is true. Does this make
sense? So i have the IF part figured out, that's all good! But i just need
to add the IFs across all the spread sheets without having to enter them
individually in the formula on teh sum sheet. Is there a way?
Even a countif could work, but then i need the sum of countif.

Thanks!

-j

 

[Bernie Deitrick]

Jane,

The simplest and easiset to maintain solution is to use a helper formula.

Select all your sheets, and select an otherwise blank cell, say E3. Then
type the formula

=IF(B72,E2,0)

That formula will be entered on every sheet. Then use the formula

=SUM1:999!E3)

To have the formula continue to work as you add more sheets, always add them
between sheet 1 and sheet 999.

HTH,
Bernie
MS Excel MVP

 

[Jane]

yes, i think that i'll have to do that. Thanks for you help. If there are
any other ways to do what I'm trying to do without having to do a separate IF
on each page, please let me know, thanks!

-j

 

[Bernie Deitrick]

Jane,

You can only use a select number of functions with 3D names and,
unfortunately, 3D names cannot be used with SUMIF formulas.

The much better solution, and one that I always try to advocate, it to use
only one sheet, with a database, but converting to a database-style requires
a different type of interaction with the spreadsheet.

Bernie