IF and SUMPRODUCT help needed

[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".

 

[Guest]

try using fx button >insert function> choose if>place your arguments> then
let us see the result

 

[Guest]

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.

 

[Bob Phillips]

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)

 

[Guest]

zzzzzzz ?

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)