Sum If and Circular Reference

UT_Princess

New Member
Joined
Nov 6, 2019
Messages
1
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

New Member
Joined
Dec 2, 2019
Messages
37
Office Version
2013
Platform
Windows
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:
Code:
=SUMIF('month results'!B:B,example!A14,'months results'!D:D)-SUM(C2:C13,C15:C33)
Regards,
Elaszat
 

Forum statistics

Threads
1,086,116
Messages
5,387,922
Members
402,088
Latest member
poppa57

Some videos you may like

This Week's Hot Topics

Top