Modify formula to reflect unique entires

**RodneyW** · 10-16-2010, 04:49 PM

This formula

=SUMPRODUCT((E$4:E$73=1)*($D4:$D73="KVRV"))

is working perfect. I'm using it five different times with the KVRV section changed to reflect the five different options that can go in column D.

Now I need to have a total of UNIQUE entries in Column B where Column E=1. Something like this:

=SUMPRODUCT((E$4:E$73=1)*($B4:$B73="different unique entries"))

Any ideas?

Thanks

**ratcat** · 10-16-2010, 10:01 PM

G'day

Since your using excel 2007 use the Countifs formula

Or if your sheet needs to be converted back to 2003 try

=SUMPRODUCT((E$4:E$73=1)*($B4:$B73="A")+(E$4:E$73=1)*($B4:$B73="b")+(E$4:E$73=1)*($B4:$B73="c")+(E$4:E$73=1)*($B4:$B73="d")+(E$4:E$73=1)*($B4:$B73="e"))

Tho I'm not clear on how your achieving in finding your unique.

Just throwing an idea around.

Cheers

RC

**DonkeyOte** · 10-17-2010, 03:28 AM

Originally Posted by RodneyW

Now I need to have a total of UNIQUE entries in Column B where Column E=1.

As RC mentions - if you're using XL2007 as implied by profile you can make use of COUNTIFS - in this particular context along lines of:

=SUMPRODUCT((($E$4:$E$73=1)*($B$4:$B$73<>""))/COUNTIFS($B$4:$B$73,$B$4:$B$73&"",$E$4:$E$73,$E$4:$E$73&""))

If you prefer to avoid COUNTIFS for sake of backwards compatibility you can use either of the below

=SUMPRODUCT(($E$4:$E$73=1)*($B$4:$B$73<>"")*(MATCH($B$4:$B$73&"",$B$4:$B73&"",0)=(ROW($B$4:$B$73)-ROW($B$4)+1)))
confirmed with Enter

=SUM(IF(FREQUENCY(IF(($E$4:$E$73=1)*($B$4:$B$73<>""),MATCH($B$4:$B$73,$B$4:$B$73,0)),ROW($B$4:$B$73)-ROW($B$4)+1)>0,1))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)