Count Pairs

[adam]

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.

[HansV]

Are the values in K2 and L2 the desired result? If so, please explain why K2 = 3 and not 4.

[adam]

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.

[HansV]

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?

[adam]

The data for the formula to calculate will always be from the same date.

[HansV]

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.

Test.xlsx

[adam]

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.

[HansV]

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?

[adam]

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.

[HansV]

Sheesh, you're complicating it even further.

But WHY do you want to do this? What are you going to use it for?

[adam]

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.

[HansV]

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.

Test.xlsm

[adam]

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.

[HansV]

Try adding the line

Code: Select all

    Application.Volatile

at the beginning of the function, below the line Function CountPairs.

[adam]

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?

[HansV]

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.

Test.xlsm

[adam]

That worked perfect Hans. Thankyou so much.