CODEWITHSUNDEEP
excelexcel-formulaexcel-2013
SkysLastChance
SkysLastChance

Reputation: 221

How can I find clients that have bought three of a certain item and only those 3 items with excel formula

I have a question similar to my last. I am trying to figure out how to find customers who have bought only tape, pen and stapler once and nothing else.

Name    CustmoerID  Item Name   Item #  Desired Results
Bob     1           Pen         555 
Bob     1           Stapler     222 
Bob     1           Stapler     222 
Bob     1           Tape        111 
Greg    2           Pen         555 
Greg    2           Pen         555 
Greg    2           Stapler     222 
Tim     3           Stapler     222 
Tim     3           Tape        666 
Tim     3           Glue        333 
Mark    4           Pen         555     Check
Mark    4           Stapler     222     Check
Mark    4           Tape        111     Check

This is the closet I think I got. But no luck.

=IF(AND(OR(D2={111}),COUNTIF(B:B,B2)=1)*AND(OR(D2={222}),COUNTIF(B:B,B2)=1)*AND(OR(D2={555}),COUNTIF(B:B,B2)=1),"Check","")

Edit- This got me a bit closer but still not working properly. 

=IF(AND(OR(D11={111,222,555}),COUNTIF(B:B,B11)=3)*AND(OR(D11={111,222,555}),COUNTIF(B:B,B11)=3)*AND(OR(D11={111,222,555}),COUNTIF(B:B,B11)=3),"Check","")

Upvotes: 0

Views: 1034

Answers (3)

jeffreyweir
jeffreyweir

Reputation: 4834

If you wanted the folk that had purchased any one of these items, but not 1 of each of the three items, you could use a 'Traditional' PivotTable:

enter image description here

That's just a PivotTable with both an item filter applied (using a Slicer) and a Values Filter applied, to only show results where count = 1

Note that you need to check the Allow Multiple Filters Per Field option of the PivotTable Options>Totals & Filters dialog in order to set both a Filter and a Values Filter at the same time.

enter image description here

If you wanted folk who had purchased exactly one of each of the three items, you could either use a traditional PivotTable with a formula running down the side (which doesn't hold any advantages to simply using a formula like Scott Craner suggests with his answer), or you could use an OLAP PivotTable and write Measures using DAX. This fairly new capability is simply awesome: You can now write complicated formulas right within a PivotTable itself.

If using the PivotTable + Formula approach, it would look something like this:

![enter image description here

...with the formula being:

=AND(GETPIVOTDATA("Item Name",$B$1,"Name",B3,"Item #",C$2)=1,GETPIVOTDATA("Item Name",$B$1,"Name",B3,"Item #",D$2)=1,GETPIVOTDATA("Item Name",$B$1,"Name",B3,"Item #",F$2)=1,GETPIVOTDATA("Item Name",$B$1,"Name",B3)=3)

But there's really no advantage between this and the great answer Scott Craner has given.

Or you can use DAX measures and an OLAP PivotTable based on the DataModel.

This requires you either to have a Excel that has the DataModel built in (i.e. Excel 2013 or later) or a version of Excel that has the PowerPivot add-in installed (Free for Excel 2010, built in to other Excel versions if you have the right premium version such as Professional Plus etc).

Here's the result:

enter image description here

...and here's the measures I used: enter image description here

...and here's how to add a measure if you don't have PowerPivot installed (but have Excel 2013 or later): You simply right click here:

enter image description here

This requires you to check the "Add to Data Model" option when you first make your PivotTable:

enter image description here

UPDATE May 2018:

I posted a question on how to do this more dynamically at https://community.powerbi.com/t5/Desktop/Identify-customers-who-had-puchased-exactly-one-unit-of-three/td-p/407941 and got 4 great answers. All four work with Excel 365, but only the third answer from Phil Seamark worked in my build of Excel 2016. Here it is:

enter image description here

It works by using the old "parameter arg" trick of using 1, 2, 4, .... in a second column of Table2 so you could see if everything someone had purchased added to 7. Sneaky!

And here's the measure:

=COUNTROWS(
  FILTER(
    SUMMARIZE(
        'Table1',
        Table1[Customer],
        "Score", SUM('Table2'[ID]),
        "myRows",COUNTROWS('Table1')
   ),[Score]=SUM(Table2[ID]) && [myRows]=COUNTROWS(Table2))
 )

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152605

This allows the use of two codes for the same thing and checks if there are three.

=IF(AND(COUNTIF(B:B,B2)=3,SUMPRODUCT(--(COUNTIFS(B:B,B2,D:D,{222,111,777,555})=1))=3),"Check","")

enter image description here

Upvotes: 2

Ion2Atom
Ion2Atom

Reputation: 11

You can use the following formula to determine if a given customer has order one and only one of a give item using the following formula (in this case for Pens):

=COUNTIFS(B:B, B2,D:D, 555)=1

You can then translate this to staplers and tape as well. The easiest way would be to put each of these into their own column and use an AND statement, but all together it would look like:

=AND(COUNTIFS(B:B, B2,D:D, 555)=1, COUNTIFS(B:B, B2,D:D, 222)=1, COUNTIFS(B:B, B2,D:D, 111)=1)

Upvotes: 1

Related Questions