Excel Formula Help

C

carmonaj

Can excel add columns on multiple worsheets in a workbook if certia
criteria are met? For example I have a workbook with worksheets 1-3
that contain all my data and on sheet 32 I want to compile that dat
into one spreadsheet, I have tried using sumif and I get a ref error
here is the formula I am using
=IF('1:31'!K7:K200=98,"sum+'1:31'!F7:F200",0)...
 
B

Biff

Hi!

Try this:

=SUMPRODUCT(SUMIF(INDIRECT(ROW(1:31)&"!K7:K200"),98,INDIRECT(ROW(1:31)&"!F7:F200")))

An alternate method: Put a Sumif on each sheet in the same cell then just
sum those cells:

In cell A1 of each sheet:

=SUMIF(K7:K200,98,F7:F200)

Then, on the summary sheet:

=SUM(1:31!A1)

Biff
 
Top