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!

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")))

--
Biff
Microsoft Excel MVP


"Jane" wrote in message
...
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!

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)+...+('99 9'!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!

"T. Valko" wrote:

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")))

--
Biff
Microsoft Excel MVP


"Jane" wrote in message
...
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!

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)


--
Biff
Microsoft Excel MVP


"Jane" wrote in message
...
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)+...+('99 9'!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!

"T. Valko" wrote:

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")))

--
Biff
Microsoft Excel MVP


"Jane" wrote in message
...
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!

yeah, it didn't work :(

"Jane" wrote:

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!

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.

"Jane" wrote in message
...
yeah, it didn't work :(

"Jane" wrote:

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!

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

"Fred Smith" wrote:

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.

"Jane" wrote in message
...
yeah, it didn't work :(

"Jane" wrote:

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!

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

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

-jane

"Herbert Seidenberg" wrote:

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