Sumif Inception!

souza93

New Member
Joined
Aug 15, 2016
Messages
4
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!


Reference IdExternal IDContract NumberInternal IDContract StatusQtyTotal Qty
121a-118CLOSED12
121a-118CLOSED12
221a-118CONFIRMED14
221a-118CONFIRMED14
321a-118CONFIRMED14
321a-118CONFIRMED14

<colgroup><col span="3"><col><col><col></colgroup><tbody>
</tbody>

thank you!
Eli
 

Silverback1992

New Member
Joined
Nov 28, 2015
Messages
13
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

New Member
Joined
Aug 15, 2016
Messages
4
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

New Member
Joined
Nov 28, 2015
Messages
13
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
 
Last edited:

souza93

New Member
Joined
Aug 15, 2016
Messages
4
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.


Reference IdExternal IDContract #Internal IDContract StatusQtyTotal QtyTotal Qty
121a-118CLOSED122
121a-118CLOSED122
221a-118CONFIRMED144
221a-118CONFIRMED144
321a-118CONFIRMED144
321a-118CONFIRMED144
422a-231CONFIRMED162
422a-231CONFIRMED162
523a-231CONFIRMED164
523a-231CONFIRMED164
623a-231CONFIRMED164
623a-231CONFIRMED164

<tbody>
</tbody>

<tbody>
</tbody>
Thank you!!
Eli
 

Silverback1992

New Member
Joined
Nov 28, 2015
Messages
13
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

New Member
Joined
Aug 15, 2016
Messages
4
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
 

Forum statistics

Threads
1,081,705
Messages
5,360,759
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top