How to count cells that meet multiple criteria, leaving out duplicates

[jsgray]

I am using the following formula to populate cell D2 in Excel 2007:

=SUMPRODUCT(($A$103:$A$65536=$A2)*($B$103:$B$65536>0))

If column A rows 103 thru 65536 matches the heading in A2 AND there is a name populating in column B then I want the cell counted. The issue I am running into is that there are duplicate names in column B. I only want to count a name once if it duplicates (or triplicates for that matter). This formula is repeated several times over multiple worksheets. I say this because macros is very tedious to capture a formula like this where it will have to populate over 500 diffent cells over 6 worksheets. Also, this is one of 8 different formulas that feed into yet another spreadsheet. Is there any assistance that you could offer to hone the criteria.

Any and all assistance is appreciated.

[Palmetto]

=sumproduct(($a$103:$a$65536=$a2)*($b$103:$b$65536<>"")/countif($b$103:$b$65536,$b$103:$b$65536&""))

[jsgray]

Thank you for this formula, it is closer then I got, but not quite there.

=SUMPRODUCT(($A$103:$A$65536=$A2)*($B$103:$B$65536<>"")/COUNTIF($B$103:$B$65536,$B$103:$B$65536&""))

This is what I have typed in. My questions are as follows:
1. why is it better to use <> to nothing, then greater then zero (or does it matter)?
2. what if there are no duplicates? (#div/0! error)
3. As is the answer I should get is 17, this produces an answer of 14.75, which if counting up cells, there should not be a fraction.

* Is there a way to add all of the rows up and subtract the duplicates:

I appreciate your quick response. Thank you for all time and effort you can afford.

[jsgray]

I have tried other methods for this post and they seem to be on the right track, but I am just not there 100%.

This gives me the right answer for only one criteria, the column B criteria:
=SUM(IF(FREQUENCY(IF(LEN($B$103:$B$65536)>0,MATCH($B$103:$B$65536,$B$103:$B$65536,0),""), IF(LEN($B$103:$B$65536)>0,MATCH($B$103:$B$65536,$B$103:$B$65536,0),""))>0,1)))

The problem is I need it to meet 2 criteria. I have tried this:
=SUMPRODUCT(($A$103:$A$65536=$A2)&(IF(FREQUENCY(IF(LEN($B$103:$B$65536)>0,MATCH($B$103:$B$65536,$B$103:$B$65536,0),""), IF(LEN($B$103:$B$65536)>0,MATCH($B$103:$B$65536,$B$103:$B$65536,0),""))>0,1)))
But I get an error message #N/A. I don't understand why other then it has to do with the sumproduct and the first criteria (Column A)

[jsgray]

Anyone in forum world that can help?

I am trying to count them amount of unique text cells in column B not including the blanks.
I use this formula and it seems to work (although a little bulky).
=SUM(IF(FREQUENCY(IF(LEN($B$103:$B$1000)>0,MATCH($B$103:$B$1000,$B$103:$B$1000,0),""),IF(LEN($B$103:$B$1000)>0,MATCH($B$103:$B$1000,$B$103:$B$1000,0),""))>0,1))

This formula works and returns the number I expect. The issue is that I also need it to fit another criteria. That criteria is:

($A$103:$A$1000=$A2)

How do I merge these multiple criteria to get the number I am interested in?

I do have Excel 2007 and this formula gives me the number of people involved in an event, but it includes the duplicates.
=COUNTIFS($A$103:$A$996,"="&$A2,$B$103:$B$996,">""")

I am attaching a spreadsheet with random data to show the example that I am using. They have the formulas listed here in cell B2 and B3.

ANY and all assistance is appreciated.

Signed frustrated and bald.

[daddylonglegs]

Try this version

=SUM(IF(FREQUENCY(IF($A$103:$A$65536=$A2,IF($B$103:$B$65536<>"", MATCH($B$103:$B$65536,$B$103:$B$65536,0))),ROW($B$103:$B$65536)-ROW($B$103)+1),1))

confirmed with CTRL+SHIFT+ENTER

[jsgray]

DaddyLongLegs,

you are my hero. Thank you for your help. It is greatly appreciated.

[antagonanin]

Try this version

=SUM(IF(FREQUENCY(IF($A$103:$A$65536=$A2,IF($B$103:$B$65536<>"", MATCH($B$103:$B$65536,$B$103:$B$65536,0))),ROW($B$103:$B$65536)-ROW($B$103)+1),1))

confirmed with CTRL+SHIFT+ENTER

Hi Daddylonglegs,

I've tried to use your formula but it's not working for me. my case is slightly different. the formula i need (based on your example) is below

=SUM(IF(FREQUENCY(IF($H$8:$H$2381>AQ2383,IF($H$8:$H$2381<AQ2384,$AD$8:$AD$2381="paid", MATCH($L$7:$L$2381,$L$7:$L$2381,0))),ROW($L$7:$L$2381)-ROW($L$7)+1),1))


basically,
1-I verify that on column H, the date is bigger than AQ2383
2-same thing as #1, verifying in column H that the date is smaller than AQ2384
3-then i want from column AD, only "paid" text. (and what do i need to do if i want "paid" and "cancelled"??)
4-this is where i get stuck, i want the count of unique values in column L.

i've also tried the formula below with sumproduct and it gives me correct value, but it counts all the duplicates. I don't know how i can have it count the unique values in column L..
=SUMPRODUCT(($H$8:$H$2381>=D2)*($H$8:$H$2381<=D3)*($AD$8:$AD$2381={"paid","cancelled"}))

thanks a lot in advance for your support

[Fotis1991]

antagonanin

Welcome to the forum

Unfortunately as per forum rules you have to start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.