circular reference calculation error

A

Alexey

Please help me to resolve circular reference calculation error

A B C
1 vegetable
2 fruit apple 5
3 fruit
4 fruit orange 6
5 vegetable potatoes 5
6 vegetable onion 6

When I place next formula to the column C in empty cells I receive circular
reference error How can I avoid it?
C1 = SUMPRODUCT(C1:C5 * (A1:A5 = B1))
 
A

Ashish Mathur

Hi,

I am not quite sure of what you are trying to do but it looks like you would
like to sum up the amount in column C, where "vegetable" if found in column
in column A. If this is indeed the case, then you can use the SUMIF()
function.

Also, in the way you have written the formula, the circular reference error
is bound to arise because while in cell C1, you are referring to range C1:C5

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
F

Fred Smith

If all you want to do is resolve the circular reference, move it to a
different cell. However, this is unlikely to solve your problem. To do that,
you need to tell us what problem you want solved. Without knowing that,
we're only guessing. My guess is you want the following in C1:

SUMPRODUCT(C2:C6 * (A2:A6 = B1))

Regards,
Fred.
 
Top