Hi All,
I have an issue with SUMIFs. I am trying to sum up a sub group of items that use the same main component i.e. apple. However, I only want to sum up the qty leftover from the main component item such as apple that is positive with no negative values.
The reason being is that the demand is on the finished good (pies,fritters,juice) not on the component i.e. apple.
=SUMIFS(B:B,C:C,[@[Sub Group]])
Here is the data set:
<colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"]Apple[/TD]
[TD="align: right"]-40[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]Apple[/TD]
[TD="align: right"]-40[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"]Apple[/TD]
[TD="align: right"]-40[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]Banana[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Banana[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
</tbody>
I have an issue with SUMIFs. I am trying to sum up a sub group of items that use the same main component i.e. apple. However, I only want to sum up the qty leftover from the main component item such as apple that is positive with no negative values.
The reason being is that the demand is on the finished good (pies,fritters,juice) not on the component i.e. apple.
=SUMIFS(B:B,C:C,[@[Sub Group]])
Here is the data set:
A. Item | B. Qty Leftover | C. Sub Group | D. Qty of Sub Group | E. Correct Qty of Sub Grp | F | G | H | I | |
Apple | |||||||||
Apple Pie | |||||||||
Apple Juice | |||||||||
Banana | |||||||||
Banana Pie | |||||||||
Banana Fritter | 4 | Banana | 11 | 11 | |
<colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"><col style="width: 47px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"]Apple[/TD]
[TD="align: right"]-40[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]Apple[/TD]
[TD="align: right"]-40[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"]Apple[/TD]
[TD="align: right"]-40[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]Banana[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]Banana[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
</tbody>