Sum If and Circular Reference

[UT_Princess]

I am looking for a "less manual" way to do my current process. I have a sales channel "NHOM" or "NHAF" and multiple material groups "W01 or "W02" for example. I pick out the main material groups and then any others are grouped as "Other". There are different types and count of material groups per channel. At the end of the month I look up all the channel/material groups with a sumifs formula and then for any "Other" material groups I take the total of the entire channel and subtract out the total of the material groups above. AI have about 50 channels that I have to go through and manually update this "Other" line for. I am looking for a formula that would take the total of the channel and subtract out any items I have already allocated to a material group and give me what is remaining - basically what I am manually doing in cells C14 (=SUMIF('month results'!B:B,example!A14,'month results'!D:D)-SUM(C2:C13)) and C34 (=SUMIF('month results'!B:B,example!A34,'month results'!D:D)-SUM(C15:C33)). I tried to do a sum if for column C but I get a circular reference.

Example:

Channel Material Group Units
NHOM W01 116,483
NHOM W02 62,448
NHAF AGA 2,864
NHAF Other 500
etc etc


Thanks in advance for your help.

-Marlo

 

[Elaszat]

Hi Marlo,

is the value in example!A14 and example!A34 the same? If so, you can group the sums in the formulas like this:

=SUMIF('month results'!B:B,example!A14,'months results'!D:D)-SUM(C2:C13,C15:C33)

Regards,
Elaszat