Countif problem?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings, and tia for your help.

The example below illustrates my problem:

I would like a formula to count how many customer/product combinations for
each record. In the example below, for instance, there are 3 records for
customer 101 and product 'c'. The formula result for each occurance would be
3. I'm trying variations on the countif array formula but no joy so far. I
know you guys will crack this while I'm still floundering around.

Customer product formula result
101 c 3
102 a 2
100 a 1
101 c 3
100 b 1
101 c 3
102 a 2
101 e 1
 
Greetings, and tia for your help.

The example below illustrates my problem:

I would like a formula to count how many customer/product combinations for
each record. In the example below, for instance, there are 3 records for
customer 101 and product 'c'. The formula result for each occurance would be
3. I'm trying variations on the countif array formula but no joy so far. I
know you guys will crack this while I'm still floundering around.

Customer product formula result
101 c 3
102 a 2
100 a 1
101 c 3
100 b 1
101 c 3
102 a 2
101 e 1
Click to expand...

If your data is in A1:B8, enter the following formula in C1 and drag/copy it
down to C8.

=SUMPRODUCT(($A$1:$A$8=A1)*($B$1:$B$8=B1))

Alter your references according to your actual data, but note that the entire
column is an absolute reference, and the Customer and Product is a relative
reference.


--ron
 
Thanks very much indeed,
worked like a dream
Click to expand...

You're welcome. Thank you for the feedback and also for the clear description
of your problem. The latter is 90% of the battle, in my opinion.


--ron
 
Back
Top