SUMPRODUCT with multiple logic and without duplicates

**cberas** · 01-25-2013, 03:11 PM

Help! I am trying to create a formula that counts how many customers without duplicates I have in column G, given that it has "PREM" in column W, and "00203" in column P.

I have been using this formula until I recently realized that there are dupliate accounts in column G, it worked well until then.

=SUMPRODUCT((W2:W4110<>" STD")*(P2:P4110<>"00201")*(P2:P4110<>"00202")*(P2:P4110<>"00204"))

I would also love to also include a "do not count empty spaces" too.

Please help, there might be another better formula to use but I am running out of ideas in this one.

Thanks!
Cristina

**Tony Valko** · 01-25-2013, 03:19 PM

Try this array formula**:

=SUM(IF(FREQUENCY(IF(W2:W4110="Prem",IF(P2:P4110="00203",MATCH(G2:G4110,G2:G4110,0))),ROW(G2:G4110)-ROW(G2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.