Sum for Duplicate Values Meeting Given Criteria

[sp0ck1]

Hello!

Problem I wanted to solve today:

A list contains sales of 19 different types, with names, phone numbers, salesperson, etc. Some lines will have a separate entry as an additional type, which will have the same phone number, name, salesperson entered, but different amount sold and type, i.e. if someone buys a ticket but adds on a donation, these will appear as two separate entries. One for the ticket, one for the donation.

In this instance, I'm looking for the sum of money generated in donations in relation to sales of six different types. If the TYPE column = "DON" and this sale's phone number is found in another row's phone number column where the type column equals one of these six types {"4PP", "TP", "LAWN", "CHAIR", "STP", "FP"}, sum for the amount in the row containing the donation.

I imagine this is some combination of SUMIF and COUNTIF but I'm just having trouble wrapping my head around it.

I ended up just taking the sales matching these types, along with all of the donations, into a new table, then sorting and dragging a formula down alongside all of the donations to sum if the phone number showed up twice in the table, since the new table contained only those types. But I'd love to know how to simplify this process for next time.

Thank you!
Cory

[jason.b75]

Having a bit of trouble getting my head around the layout. Can you post a sample file please.

Remember to use fictional data for your sample, but make it so that it is a true representation or the real data.

To attach the file, click 'Go Advanced', then scroll down a bit and click 'Manage Attachments'

[sp0ck1]

Yeah, I was having a lot of trouble explaining this.

File is attached. Phone numbers are all randomized, names removed. I want to know the sum of Donations (type DON) made where the phone number is shared between a sale of those six types specified previously. Donations are marked "A" in the NURA column, meaning "Additional" as they are in addition to the main sale.

Hope this helps! I'm confused and I need to put it in someone else's hands because I'm sure I can use the formula in the future.

Thank you!

Cory

[FDibbins]

I just did a quick filter on A = DON, then did a countifs based on DON and the phone number - I got no duplicates. Could you share some sample answers please?

[jason.b75]

It doesn't work with a single formula, I thought that I had it, but the results were duplicating / triplicating where the phone number matched multiple types in the list.

With an extra helper column, enter this formula into G2, then fill down, this will isoloate the qualifying donations

=IF(A2="DON",(SUM(COUNTIFS($A$2:$A$695,{"4PP","TP","LAWN","CHAIR","STP","FP"},$E$2:$E$695,E2))>0)*B2,0)

Then you simple need to total that column with =SUM(G2:G695)

edit:-

That compares donations to the other types listed, but just notice that you had other matching criteria in your question as well. Might need to go back to the beginning.

[FlameRetired]

I think this does what you want.

In H1 is a drop down of all the TYPES. In I1 is a sum of the results in column I.

In G2 and filled down is a helper column. It identifies in what rows "DON" (or what ever type you choose in H1) there are duplicate phone numbers not of "DON" or what ever type.

Please Login or Register  to view this content.

The next goes in H2 and is filled down and across column I. In column H it returns the qualifying types from {"4PP","TP","LAWN","CHAIR","STP","FP"} only. In column I the corresponding amounts.

Please Login or Register  to view this content.

[soledad]

I think it may be this

Please Login or Register  to view this content.

Array formula, confirm it by pressing CTRL+SHIFT+ENTER simultaneously
Hope this help

[jason.b75]

Looking at the replies, I think that everyone has missed the same thing that I did initially.

The way I read your question the second time is that donations should only be included if they are part of the same transaction, so if the donation and other sales types are on different dates then they should be excluded, even if the prone number is the same?

Got it down to a long formula in a single cell, (inspired by one of Glenn Kennedy's suggestions in another thread)

=SUMPRODUCT(($A$2:$A$695="DON")*((
COUNTIFS($F$2:$F$695,$F$2:$F$695,$E$2:$E$695,$E$2:$E$695,$A$2:$A$695,"4PP")
+COUNTIFS($F$2:$F$695,$F$2:$F$695,$E$2:$E$695,$E$2:$E$695,$A$2:$A$695,"TP")
+COUNTIFS($F$2:$F$695,$F$2:$F$695,$E$2:$E$695,$E$2:$E$695,$A$2:$A$695,"LAWN")
+COUNTIFS($F$2:$F$695,$F$2:$F$695,$E$2:$E$695,$E$2:$E$695,$A$2:$A$695,"CHAIR")
+COUNTIFS($F$2:$F$695,$F$2:$F$695,$E$2:$E$695,$E$2:$E$695,$A$2:$A$695,"STP")
+COUNTIFS($F$2:$F$695,$F$2:$F$695,$E$2:$E$695,$E$2:$E$695,$A$2:$A$695,"FP"))>0)*$B$2:$B$695)  

Which should work if my theory above is correct.

I thought that I would be able to get it with something like this, which is effectively a condensed version of the same formula

=SUM(($A$2:$A$695="DON")*(COUNTIFS($F$2:$F$695,$F$2:$F$695,$E$2:$E$695,$E$2:$E$695,$A$2:$A$695,{"4PP","TP","LAWN","CHAIR","STP","FP"})>0)*$B$2:$B$695)  

Array confirmed with Shift Ctrl Enter, but, as with my first aborted attempt, it is duplicating the donations when there is more than 1 other matching transaction.

[sp0ck1]

Donations should be included if they are part of the same transaction sounds right. Someone buys tickets for one of those six types and throws in a donation, great. Someone buys tickets for one of the other 13 types and throws in a donation, don't count it here. If a donation's phone number matches a phone number for a TP, etc. then I want the dollar amount. If they call back on another date to add a donation to that order, that's fine too, but an extreme rarity.

Usually a DON entry will only match with one phone number, but occasionally someone buys several things on one order besides the donation, so it does complicate things a bit more.

This wouldn't be an issue if the two separate campaigns weren't combined into one sheet by someone else, but it's too late now.

I think that long single cell formula is what I was unable to wrap my head around. Looks like what I would have created if I understood multiplying two formulas/using SUMPRODUCT already. I looked at the evaluation a bit, and still a little confused, but the total it gives matches up with the total I got by isolating the types and running a formula down the side of the donations to return the AMT column if the number showed up anywhere in the isolated types' phones.

Thank you all so much for your help! Even the "wrong" answers provide learning opportunities.

[jason.b75]

In this instance, you would need soledad's formula from post #7 to include the donation that is separate from the order.

If they call back on another date to add a donation to that order, that's fine too, but an extreme rarity.

Different scenario, Somebody buys TP with no donation today, then next week buys one of the other 13 non-qualifiying types with a donation (count, or no?)
What if those 2 transactions were the same phone number but different person? (Spouses using a home landline instead of personal cell for example).

Things like this might need varaitions of my long formula, or one of the multiple formula methods (my first suggestion, of FlameRetired's). If you're not aware, it is quite common that a whole column of simple formulas will process a complex task much easier, and faster than 1 single formula. Understandably, there may be times when this method is not practical.

[sp0ck1]

Hmm, if they bought something alongside one of the non-qualifying types, that wouldn't count here. That could have happened already, actually, since some of the lead base is shared between the two projects.

It's feasible that a different name could be attached to the number in one database than in the other. Again, if they both buy something, then the type will come into play and only the purchase matching one of the six types should be counted. As it is, all donations are being counted toward the Fall campaign even if they were taken during the Summer campaign, and the task was to see if the somewhat lackluster Summer campaign's numbers would have been boosted significantly if it got to keep its donations attributed to it. If they'd been coded separately in the first place (or not merged into one sheet...) then this would never have been an issue. A strange decision he made that day.

I do often use columns of simple formulas for other tasks, but want a single formula if a cell is going to remain on a sheet and keep updated values as the list of sales grows and changes. This was posed as much as a challenge for myself and a learning experience than as a permanent solution to this problem. I appreciate all of the attention to detail here greatly.

[jason.b75]

You should be able to manipulate my long formula do incude or exclude columns of data as required, the key being the 6 COUNTIFS() functions.

COUNTIFS($F$2:$F$695,$F$2:$F$695,$E$2:$E$695,$E$2:$E$695,$A$2:$A$695,"4PP")

Looking at them you will see that they are all identical with the exception of the type criteria. The other sections all use the full range of data as both the range and the criteria, comparing every phone number to every phone number and every date to every date. If your wanted to check that names matched as well then you can add in another range and criteria in the same method.

This formula will get very resource heavy as the data volume increases so might be best used in conjunction with dynamic named ranges, or by using a data table if that is practical.
If the calculation delay gets to the point of unacceptably noticeable then you might have to bite the bullet and go with the helper column method.