Count Formula

[chrysti]

I am using a sumproduct (without the range to sum) to count a list of information that I have, but the issue that I am having is that because this list has everything that we carry and who we have sold it to, things appear in the list more than once. I only want to know how many different items we sold, not how many times we sold that item...is there anyway to get the formula to count only the first time that it encounters a style on the list? They all have different names so it is hard to say only count this, because I am wanting to do it for hundreds of different styles and thousands of sales.

Thanks in advace for the help
Chrysti

 

[chrysti]

SUMPRODUCT(--(Season1!$A$2:$A$65536=COVER!$B$11),--(Season1!$B$2:$B$65536=COVER!C$14),--(Season1!$C$2:$C$65536=COVER!C$15),--(Season1!$H$2:$H$65536=$A16),--(Season1!$F$2:$F$65536=COVER!$C$11),--(Season1!$E$2:$E$65536=1),(Season1!$D$2:$D$65536<>""))/COUNTIF(Season1!$D$2:$D$65536,Season1!$D$2:$D$65536&"")

This is what I have tried and it doesn't work but I need to have all of these filters in place so that I only get the information that I want!

Help please

Thanks
Chrysti

 

[mgirvin]

Maybe you have one parenthesis in the wrong place:

=SUMPRODUCT(--(Season1!$A$2:$A$65536=COVER!$B$11),--(Season1!$B$2:$B$65536=COVER!C$14),--(Season1!$C$2:$C$65536=COVER!C$15),--(Season1!$H$2:$H$65536=$A16),--(Season1!$F$2:$F$65536=COVER!$C$11),--(Season1!$E$2:$E$65536=1),(Season1!$D$2:$D$65536<>""))/COUNTIF(Season1!$D$2:$D$65536,Season1!$D$2:$D$65536&"")

Try this:

=SUMPRODUCT(--(Season1!$A$2:$A$65536=COVER!$B$11),--(Season1!$B$2:$B$65536=COVER!C$14),--(Season1!$C$2:$C$65536=COVER!C$15),--(Season1!$H$2:$H$65536=$A16),--(Season1!$F$2:$F$65536=COVER!$C$11),--(Season1!$E$2:$E$65536=1),(Season1!$D$2:$D$65536<>"")/COUNTIF(Season1!$D$2:$D$65536,Season1!$D$2:$D$65536&""))

I have my fingers crossed!!