IF and SUMPRODUCT help needed
 

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

--
George

IF and SUMPRODUCT help needed
 

try using fx button insert function choose ifplace your arguments then
let us see the result

"George" wrote:

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

--
George

IF and SUMPRODUCT help needed
 

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.




"George" wrote:

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

--
George

IF and SUMPRODUCT help needed
 

You could copy the formula to another cell, then CopyPastespecial Values to
the other cell.

--

HTH

Bob Phillips

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

"George" wrote in message
...
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".

--
George

IF and SUMPRODUCT help needed
 

zzzzzzz ?

"Bob Phillips" wrote:

You could copy the formula to another cell, then CopyPastespecial Values to
the other cell.

--

HTH

Bob Phillips

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

"George" wrote in message
...
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".

--
George