Sum with some criteria

[freddocp]

Please, I am having some challenges with a sum with a few criteria, please view the sample attached.

1- I need to sum up the same Part Numbers (column A) I made an example with Descr 2 and Descr 15.

2- Just sum up the different lots (Column B) the lot that has some repeated should not be included in the sum.

3- Status (column C) only consider the terms with ?Demand? in the sum.

In column E: the values I need as a result. For the examples Descr 2 and Descr 15

Note: Remember that you'll need to fill in the whole spreadsheet afterward.

Sum with a few criteria - Rev.0.xlsx
	G
12
Sheet1

Thank you in advance.

 

[Skyybot]

I believe a PivotTable will accomplish your task.

 

[freddocp]

Unfortunately, I have the issue of the sum of repeated values ig I go straight with the pivot. The Lots that are the same, I should not sum.

 

[hagia_sofia]

Hello, test the following if it works for you:

=LET(
part,$A$2:$A$25,
status,$C$2:$C$25,
headers,$A$1:$D$1,
f,$A$2:$D$25,
a,UNIQUE(FILTER(f,(part=A2)*(status="Demand"))),
b,CHOOSECOLS(a,XMATCH("Qantity",headers)),
IF(COUNTIFS(part,A2,status,"Demand")<=1,D2,SUM(b)))

 

[freddocp]

Hello hagia_sofia
Thank you very much!

I believe the formula is just like that, I've put into practice what I need, but it's still giving a difference, I'd be very grateful if you could help:

ED_ORDER_NUM (column A) , must be different to be summed, however, I think I had not mentioned before, ED_ITEM (column B), must be equal to be summed. And the status in this case must be “On hand”
Include the required result in column F

Help - Sum with a few criteria - Rev.2;;.xlsx
	C
12
Sheet1

 

[hagia_sofia]

I am not sure if I understand it correctly - based on the example, how is it possible that the first row is 3?

 

[freddocp]

I am not sure if I understand it correctly - based on the example, how is it possible that the first row is 3?

I understand, I don't think I've been very clear, sorry about that. I'm attaching another print, see if that's better.

 

[hagia_sofia]

Ah, many thanks for the clarification. Would this work then:

=LET(
f,UNIQUE(FILTER($A$2:$D$8,($B$2:$B$8=B2)*($C$2:$C$8="On hand"))),
IF(C2="Supply",0,SUM(CHOOSECOLS(f,XMATCH("QTY",$A$1:$D$1)))))

 

[freddocp]

Ah, many thanks for the clarification. Would this work then:

=LET(
f,UNIQUE(FILTER($A$2:$D$8,($B$2:$B$8=B2)*($C$2:$C$8="On hand"))),
IF(C2="Supply",0,SUM(CHOOSECOLS(f,XMATCH("QTY",$A$1:$D$1)))))

Hi! It works great , thank you! But I am struggling when I take to a model with more columns and try to adapt your formula. do you see where I am getting it wrong?

 

[hagia_sofia]

I see. Hopefully you could go with something like this (please adjust ranges):

=LET(
a,HSTACK($C$2:$C$5,$F$2:$F$5,$N$2:$N$5,$U$2:$U$5),
f,UNIQUE(FILTER(a,($F$2:$F$5=F2)*($N$2:$N$5="On hand"))),
IF(N2="Supply",0,SUM(CHOOSECOLS(f,4))))