SUMPRODUCT with multiple logic and without duplicates

1. SUMPRODUCT with multiple logic and without duplicates

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

2. Re: SUMPRODUCT with multiple logic and without duplicates

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.

3. Re: SUMPRODUCT with multiple logic and without duplicates

Thank you! This worked great

4. Re: SUMPRODUCT with multiple logic and without duplicates

You're welcome. Thanks for the feedback!

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1