Forum Discussion

Pierr1930's avatar
Pierr1930
Copper Contributor
Sep 21, 2020
Solved

Excel Formula help please....

I have spent a number of hours trying to make this work, without success.   Trying to set up two different criteria to count.   Column A is a list of order numbers, repeated by the number of item...
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 21, 2020

    Pierr1930 

    If your Excel version supports dynamic arrays, that could be

    with formulas

    in G2:
=UNIQUE($D$2:INDEX($D:$D,COUNTA($D:$D)))

in H2:
=COUNT(UNIQUE(
   FILTER( $B$2:INDEX($B:$B,COUNTA($B:$B)),
           $D$2:INDEX($D:$D,COUNTA($D:$D))=G2)
))
(drag it down)
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Sep 21, 2020

Pierr1930 

 

I'm not sure about the translation, but this might help you.

 

=COUNTIF(B$2:B$25,B2)

as a example see please the workbook.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

 

  • Pierr1930's avatar
    Pierr1930
    Copper Contributor
    Sep 21, 2020

    NikolinoDE 

    Thanks, but that doesn't solve this problem.

    I know already how many times a customer name appears on the list.

    The problem is knowing how many different Order numbers are associated with that customer.

    Each customer has a different number of orders.

     

    In your example, Customer # 1 appears 3 times, BUT what I want is how many orders did they place.

    In your example, Order numbers 1213 and 234234 are associated with Customer #1 so the correct answer I seek is "2" orders since 1213 was on the list twice. 

     

    Thanks for trying....

     

     

     

Resources