Sumif Inception!

[souza93]

Hi Everyone,

I maintain a contracts database and need to automate the total qty column. Having a really tough time getting it to automatically show those numbers.

Summary: This data represents one contract (internally it is contract #18, externally, it is contract #21). It is broken down into three sections (two line items each) which need to be confirmed or closed - each section is represented by a reference ID. Reference ID #1 was closed and the remaining two were confirmed. I need to be able to add up the quantities within their corresponding reference ID groups. In other words, total quantity for all Ref ID that is '1' AND 'closed' needs to add it up and show 2.

Originally, I only had to check the external ID column and do a sumif to get the total qty column. Now, I need to check.. external ID, Reference ID, and Contract Status.

Long story short, I need the #s in the total qty column to automatically reflect the below written numbers. Hopefully this explanation wasn't too confusing and any help would be immensely appreciated!


[TABLE="class: grid, width: 581"]
<colgroup><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]Reference Id[/TD]
[TD="align: right"]External ID[/TD]
[TD]Contract Number[/TD]
[TD="align: right"]Internal ID[/TD]
[TD]Contract Status[/TD]
[TD="align: right"]Qty[/TD]
[TD="align: right"]Total Qty[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]21[/TD]
[TD]a-1[/TD]
[TD="align: right"]18[/TD]
[TD]CLOSED[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]21[/TD]
[TD]a-1[/TD]
[TD="align: right"]18[/TD]
[TD]CLOSED[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]21[/TD]
[TD]a-1[/TD]
[TD="align: right"]18[/TD]
[TD]CONFIRMED[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]21[/TD]
[TD]a-1[/TD]
[TD="align: right"]18[/TD]
[TD]CONFIRMED[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]21[/TD]
[TD]a-1[/TD]
[TD="align: right"]18[/TD]
[TD]CONFIRMED[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]21[/TD]
[TD]a-1[/TD]
[TD="align: right"]18[/TD]
[TD]CONFIRMED[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

thank you!
Eli

 

[Silverback1992]

Hello Eli.

I might be missing something. Isn't the total qty of the 3rd line supposed to be 2? The reference ID is 2 there, so I don't understand how the 4 is coming.

 

[souza93]

Hello Eli.

I might be missing something. Isn't the total qty of the 3rd line supposed to be 2? The reference ID is 2 there, so I don't understand how the 4 is coming.

Hello Silverback1992!

Sorry if I didn't properly explain it.. The reason I need a 4 to show there is because it needs to add all reference id's within this contract which are confirmed. If it closed, it will add the closed (1+1) - if confirmed, it needs to add all (1+1+1+1). If one of the references was a different contract number then it would only need to show a 2.

Does that clarify things?

Thanks!
Eli

 

[Silverback1992]

Would this help?

=SUMPRODUCT($F$2:$F$7,--(CONCATENATE($C$2:$C$7,$E$2:$E$7)=CONCATENATE(C2,E2)))

Edit: or just this

=SUMPRODUCT($F$2:$F$7,--(($C$2:$C$7&$E$2:$E$7)=(C2&E2)))

Edit2: or just this

=SUMIFS($F$2:$F$7,$C$2:$C$7,C2,$E$2:$E$7,E2)

:D

 

[souza93]

Would this help?

=SUMPRODUCT($F$2:$F$7,--(CONCATENATE($C$2:$C$7,$E$2:$E$7)=CONCATENATE(C2,E2)))

Edit: or just this

=SUMPRODUCT($F$2:$F$7,--(($C$2:$C$7&$E$2:$E$7)=(C2&E2)))

Edit2: or just this

=SUMIFS($F$2:$F$7,$C$2:$C$7,C2,$E$2:$E$7,E2)

:D

Silverback1992,

Thank you very much for your quick reply! It works for now.. but I have a new challenge..

Suppose I want your formula to still be in effect (having quantities added of identical contract if.. when reference ID differs, I add quantities together based on contract status) and..

If there are a few line items of the same contract with the same contract statuses, but differing external ID's (in bold below), I need it to add quantities based on external ID's (instead of adding by reference ID's)... while still having a formula in effect that adds based on reference ID if the contract status of identical contracts differs.

In other words.. your formula currently appears as 'Total Qty (1)' column, but I need it to appear as 'Total Qty (2)' column.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Reference Id[/TD]
[TD]External ID[/TD]
[TD]Contract #[/TD]
[TD]Internal ID[/TD]
[TD]Contract Status[/TD]
[TD]Qty[/TD]
[TD]Total Qty[/TD]
[TD]Total Qty[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]21[/TD]
[TD]a-1[/TD]
[TD]18[/TD]
[TD]CLOSED[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]21[/TD]
[TD]a-1[/TD]
[TD]18[/TD]
[TD]CLOSED[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]21[/TD]
[TD]a-1[/TD]
[TD]18[/TD]
[TD]CONFIRMED[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]21[/TD]
[TD]a-1[/TD]
[TD]18[/TD]
[TD]CONFIRMED[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]21[/TD]
[TD]a-1[/TD]
[TD]18[/TD]
[TD]CONFIRMED[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]21[/TD]
[TD]a-1[/TD]
[TD]18[/TD]
[TD]CONFIRMED[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]22[/TD]
[TD]a-2[/TD]
[TD]31[/TD]
[TD]CONFIRMED[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]22[/TD]
[TD]a-2[/TD]
[TD]31[/TD]
[TD]CONFIRMED[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]23[/TD]
[TD]a-2[/TD]
[TD]31[/TD]
[TD]CONFIRMED[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]23[/TD]
[TD]a-2[/TD]
[TD]31[/TD]
[TD]CONFIRMED[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]23[/TD]
[TD]a-2[/TD]
[TD]31[/TD]
[TD]CONFIRMED[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]23[/TD]
[TD]a-2[/TD]
[TD]31[/TD]
[TD]CONFIRMED[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 789"]
<tbody>[TR]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
Thank you!!
Eli

 

[Silverback1992]

Hey Eli,

Well I've just added another criteria and criteria range to my sumifs, and I'm getting the results, you were looking for:

=SUMIFS($F$2:$F$13,$E$2:$E$13,E2,$C$2:$C$13,C2,$B$2:$B$13,B2)

 

[souza93]

Hey Eli,

Well I've just added another criteria and criteria range to my sumifs, and I'm getting the results, you were looking for:

=SUMIFS($F$2:$F$13,$E$2:$E$13,E2,$C$2:$C$13,C2,$B$2:$B$13,B2)

Hi Silverback1992,

That works perfectly! Thank you so much. I think I may have over-complicated it in my mind and explanation.

Anyway, I will run into some more tricky situations eventually. For example, contract a-1 and a-2 will have identical external references and I will have to display the data a bit differently.. Hope I can reach out to you for some guidance on that!

Thanks so much!!

Eli