I have a workbook of about 30 pages.
I am trying to make a formula that will look at the same cell on each page
and if that cell has a value of "1" to sum another cell on only the pages
with the value "1".
I have tried, Sumproduct, Sumif, and have tried both of these with the
ThreeD function as well. I keep getting a VALUE error. Any suggestions?

Can you apply the condition w/in each worksheet? For discussion's sake,
let's suppose that if there's a 1 in cell A1, your total should include cell
A2 from that worksheet. Let's use A3 as the conditional cell, so in each
sheet's cell A3 enter the IF function: =if(a1=1,a2,""). Then you should be
able to use a 3D sum of the A3 cells from each sheet.

"frustratedwthis" wrote:

I have a workbook of about 30 pages.
I am trying to make a formula that will look at the same cell on each page
and if that cell has a value of "1" to sum another cell on only the pages
with the value "1".
I have tried, Sumproduct, Sumif, and have tried both of these with the
ThreeD function as well. I keep getting a VALUE error. Any suggestions?

First, my assumptions which you will have to modify to suit your data

The cell to test is H1
The cell to accumulate is I1
There are three worksheets to test against

List the worksheets by name in M1:M3

Then use this formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&M1:M3&"'!H1"),1,IND IRECT("'"&M1:M3&"'!I1")))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"frustratedwthis" wrote in
message ...
I have a workbook of about 30 pages.
I am trying to make a formula that will look at the same cell on each page
and if that cell has a value of "1" to sum another cell on only the pages
with the value "1".
I have tried, Sumproduct, Sumif, and have tried both of these with the
ThreeD function as well. I keep getting a VALUE error. Any suggestions?

this could work but cell I6 on each sheet will contain the Number 1-6. I
would like to only sum Cell N12 on each sheet with the same value. Does that
make sense?

"bpeltzer" wrote:

Can you apply the condition w/in each worksheet? For discussion's sake,
let's suppose that if there's a 1 in cell A1, your total should include cell
A2 from that worksheet. Let's use A3 as the conditional cell, so in each
sheet's cell A3 enter the IF function: =if(a1=1,a2,""). Then you should be
able to use a 3D sum of the A3 cells from each sheet.

"frustratedwthis" wrote:

I have a workbook of about 30 pages.
I am trying to make a formula that will look at the same cell on each page
and if that cell has a value of "1" to sum another cell on only the pages
with the value "1".
I have tried, Sumproduct, Sumif, and have tried both of these with the
ThreeD function as well. I keep getting a VALUE error. Any suggestions?

I used the following and got a #REF.
=SUMPRODUCT(SUMIF(INDIRECT("'"&6-1:6-90&"'!$N$8"),1,INDIRECT("'"&6-1:6-90&"'!$N$9")))

It also keeps taking the 0's out of my tab names...they are all in the
format 0#-0#
Could the tab names be a problem?


"Bob Phillips" wrote:

First, my assumptions which you will have to modify to suit your data

The cell to test is H1
The cell to accumulate is I1
There are three worksheets to test against

List the worksheets by name in M1:M3

Then use this formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&M1:M3&"'!H1"),1,IND IRECT("'"&M1:M3&"'!I1")))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"frustratedwthis" wrote in
message ...
I have a workbook of about 30 pages.
I am trying to make a formula that will look at the same cell on each page
and if that cell has a value of "1" to sum another cell on only the pages
with the value "1".
I have tried, Sumproduct, Sumif, and have tried both of these with the
ThreeD function as well. I keep getting a VALUE error. Any suggestions?

Your not following the method. M1 to M3 (or howevere many you need) should
contain each individual sheet name, don't embed the start and end sheet
names in the formula,it will not work.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"frustratedwthis" wrote in
message ...
I used the following and got a #REF.

=SUMPRODUCT(SUMIF(INDIRECT("'"&6-1:6-90&"'!$N$8"),1,INDIRECT("'"&6-1:6-90&"'
!$N$9")))

It also keeps taking the 0's out of my tab names...they are all in the
format 0#-0#
Could the tab names be a problem?


"Bob Phillips" wrote:

First, my assumptions which you will have to modify to suit your data

The cell to test is H1
The cell to accumulate is I1
There are three worksheets to test against

List the worksheets by name in M1:M3

Then use this formula


=SUMPRODUCT(SUMIF(INDIRECT("'"&M1:M3&"'!H1"),1,IND IRECT("'"&M1:M3&"'!I1")))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"frustratedwthis" wrote in
message ...
I have a workbook of about 30 pages.
I am trying to make a formula that will look at the same cell on each
page
and if that cell has a value of "1" to sum another cell on only the
pages
with the value "1".
I have tried, Sumproduct, Sumif, and have tried both of these with the
ThreeD function as well. I keep getting a VALUE error. Any
suggestions?