IF and SUMPRODUCT help needed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following formula in sheet2, which is a summary from sheet "MAIN".
This formula is in cell "A14" this is dragged down to cell"A5000". This is
done to columns A to G.

=IF($A14="","",SUMPRODUCT((MAIN!$A$11:$A$5000=$A14)*(MAIN!$D$11:$D$5000)))

The problem Im having is when I clear contents in sheet "MAIN", it clears
the data in sheet2. Is there a way so that sheet2 keeps a cumulative total of
sheet "MAIN"?
This will enable me to just have about 100 rows in sheet"MAIN".
 
try using fx button >insert function> choose if>place your arguments> then
let us see the result
 
This formula is in cell "A14" this is dragged down to cell"A5000".

george...simpy u may be trying to shrink-in ...u cannot use $A14 as part of
logical_criteria in a formula located on A14.
 
You could copy the formula to another cell, then Copy>Pastespecial Values to
the other cell.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
zzzzzzz ?

Bob Phillips said:
You could copy the formula to another cell, then Copy>Pastespecial Values to
the other cell.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
Click to expand...
 
Back
Top