Forum Discussion
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...
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)
Thanks for trying....I am just not getting it to work....
If without dynamic arrays
when
in J2 =IFERROR( INDEX( $D$2:$D$48, AGGREGATE(15,6,1/(COUNTIF($J$1:$J1,$D$2:$D$48)=0)*(ROW($D$2:$D$48)-1),1) ),"") in K2 =SUMPRODUCT( ($D$2:$D$48=$J2)/ COUNTIF($B$2:$B$48,$B$2:$B$48) ) drag both down till empty cells appearIf 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)Pierr1930 , you are welcome
Hey thanks I think that is it.....many thanks for continuing to try.
I will play with it, but this looks very promising!!
Pierre
- If the order number that a customer makes does not mix with other customers, then you can use the first file I sent you. With the filter table you can see how many orders he has made per customer, and which ones too.
I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
