Count Pairs
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Count Pairs
Hi anyone,
In the attached workbook I'm considering "Apple" and "Orange" as one pair of fruits. Meaning, if a customer bought an apple and orange, the pair is counted as one pair of fruits.
If the customer buys only one fruit, the formula will not count them as pairs or as individual items.
I've attached the file for reference.
Any help on this would be kindly appreciated.
Note: a customer can buy the product one or more times a day.
In the attached workbook I'm considering "Apple" and "Orange" as one pair of fruits. Meaning, if a customer bought an apple and orange, the pair is counted as one pair of fruits.
If the customer buys only one fruit, the formula will not count them as pairs or as individual items.
I've attached the file for reference.
Any help on this would be kindly appreciated.
Note: a customer can buy the product one or more times a day.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count Pairs
Are the values in K2 and L2 the desired result? If so, please explain why K2 = 3 and not 4.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Count Pairs
K2 is the result heading for Male and L2 is the result heading for Female. K2 = 3 because three customers have bought the pairs.
Customer C111, Customer C666 and again Customer C111 has bought the pairs which would sum up to three male customers.
Customer C111, Customer C666 and again Customer C111 has bought the pairs which would sum up to three male customers.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count Pairs
Your example has only one date. What if a customer buys an apple on Tuesday and an orange on Wednesday? Does that count as a pair or not?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Count Pairs
The data for the formula to calculate will always be from the same date.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count Pairs
Please test the following formula in K3:
=SUMPRODUCT((COUNTIFS($B$2:$B$11,"Apple",$D$2:$D$11,$D$2:$D$11)=COUNTIFS($B$2:$B$11,"Orange",$D$2:$D$11,$D$2:$D$11))*($C$2:$C$11=LEFT(K$2)))/2
Fill to the right to L3.
=SUMPRODUCT((COUNTIFS($B$2:$B$11,"Apple",$D$2:$D$11,$D$2:$D$11)=COUNTIFS($B$2:$B$11,"Orange",$D$2:$D$11,$D$2:$D$11))*($C$2:$C$11=LEFT(K$2)))/2
Fill to the right to L3.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Count Pairs
Thankyou for the help Hans. But here's a query I have.
The customer C111 has bought "fruits" (which includes "Apple" and "orange") two times on the same date. And customer C666 has bought fruits one time.
With this the total number of fruits for male is 3.
The customer C111 again bought only "Apple" during his 4th visit on the same day.
When this data is added the sum of 3 becomes 1 in cell K3. Why does this happen. I'm assuming it should still be three as the customer C111 did not buy both Apple and orange together.
The customer C111 has bought "fruits" (which includes "Apple" and "orange") two times on the same date. And customer C666 has bought fruits one time.
With this the total number of fruits for male is 3.
The customer C111 again bought only "Apple" during his 4th visit on the same day.
When this data is added the sum of 3 becomes 1 in cell K3. Why does this happen. I'm assuming it should still be three as the customer C111 did not buy both Apple and orange together.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count Pairs
That's why I asked you to test. And why you should always provide a realistic example instead of a limited one that doesn't show all possibilities...
It turns out to be complicated. Why do you need this?
It turns out to be complicated. Why do you need this?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Count Pairs
Very much sorry for the confusion/complication. I was hoping the formula with limited data would work accordingly.
I've attached the workbook to make the request to be understood better.
In the attached workbook you can see Customer ID C113 has new products in row 4,5 and 6. These products seem to interfere with the current formula. Because with this edition instead of 4 the sum for males is coming 4.5
How could I make the formula not to include products other than "Apple" and "Orange" for the same customers.
I've attached the workbook to make the request to be understood better.
In the attached workbook you can see Customer ID C113 has new products in row 4,5 and 6. These products seem to interfere with the current formula. Because with this edition instead of 4 the sum for males is coming 4.5
How could I make the formula not to include products other than "Apple" and "Orange" for the same customers.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count Pairs
Sheesh, you're complicating it even further.
But WHY do you want to do this? What are you going to use it for?
But WHY do you want to do this? What are you going to use it for?
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Count Pairs
There are customers who will buy only apple and orange. There are also customers who will buy apple and orange with other products. But the goal of the formula is to calculate or count only the pairs; apple and orange.
I hope I'm making the question clear. Thanks in advance.
I hope I'm making the question clear. Thanks in advance.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count Pairs
I'm sure it can be done with built-in functions, but I don't see how to do that, so I created a custom VBA function.
See the attached workbook, now a macro-enabled workbook Test.xlsm.
See the attached workbook, now a macro-enabled workbook Test.xlsm.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Count Pairs
Thanks. Suppose the user adds a pair of apple and orange for a new customer C999. How does the sum change? does it change automatically or how? Im asking because I don't see the value changing?
It seems, I have to double click the cell value K3 to change the sum.
It seems, I have to double click the cell value K3 to change the sum.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count Pairs
Try adding the line
at the beginning of the function, below the line Function CountPairs.
Code: Select all
Application.Volatile
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Count Pairs
Your code works fine. But If I open another workbook while the active workbook is open. the values in K3 and L3 becomes either 0 or I get the #VALUE! error.
But if I close the active workbook and then again open it back while the other workbook is opened. it becomes ok.
Why is this happening?
But if I close the active workbook and then again open it back while the other workbook is opened. it becomes ok.
Why is this happening?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Count Pairs
Here is a new version that avoids this problem. The formula will also automatically update the result when you change a value in the source range.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07