SUMPRODUCT ???

F

frustratedwthis

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?
 
B

bpeltzer

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.
 
B

Bob Phillips

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,INDIRECT("'"&M1:M3&"'!I1")))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
F

frustratedwthis

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?
 
F

frustratedwthis

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?
 
B

Bob Phillips

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