Function including a distinct count

**bkatzman** · 05-19-2010, 02:37 PM

Greetings,

I have a spreadsheet with a tab "Data" that looks like this:

A B C D
System ID Date Amt Category
151398 1/1/08 10.00 Online - Website
151398 1/1/08 90.00 Online - Website
151399 1/1/08 30.00 Online - Website

How can I modify the following function to only count the following as 2 gifts (considering two of them have the same system ID)?

=SUMPRODUCT(--(Data!$C:$C= "Online Giving - Website"),--(Data!$A:$A>DATE(2008,1,1)),--(Data!$A:$A<=DATE(2008,1,31)))

If someone can help me out, as you have done so graciously before, that would be awesome. Otherwise I may severely SOL.

Thanks as always,
Bryce

**NBVC** · 05-19-2010, 02:50 PM

Are the column references in your formula the same as your sample? Looks like you added column A and the rest of the columns moved over 1....

If so, try:

=COUNT(1/FREQUENCY(IF(Data!$D:$D= "Online Giving - Website",IF(Data!$B:$B>DATE(2008,1,1),IF(Data!$B:$B<=DATE(2008,1,31),IF(Data!A:A<>"",MATCH(A:A,A:A,0))))),ROW(A:A)-ROW(A1)+1))

confirmed with CTRL+SHIFT+ENTER not just ENTER. Adjust ranges if necessary.

Note: with these array formulas, includind Sumproduct, you should avoid whole column referencing as these formulas are resource intensive.

**bkatzman** · 05-19-2010, 03:06 PM

Thanks for the your help. I got zero as a result. Attached is a sample to answer your question about headers/referencing.

**NBVC** · 05-19-2010, 03:21 PM

Try something like:

=COUNT(1/FREQUENCY(IF(D2:D10= "Online Giving - Website",IF(B2:B10>=DATE(2008,1,1),IF(B2:B10<=DATE(2008,1,31),IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))))),ROW(A2:A10)-ROW(A1)+1))

I used a defined range of row 2 to row 10.. adjust as necessary. Remember to confirm with CTRL+SHIFT+ENTER

Also you had >DATE(2008,1,1) when you wanted probably >=DATE(2008,1,1)

**bkatzman** · 05-19-2010, 04:12 PM

Thanks again. Your code was awesome. Unfortunately as you alluded to it will require lots of processing. So I need to find another solution.

Thanks for catching the greater than or equal to issue!

**NBVC** · 05-19-2010, 04:18 PM

Perhaps you can make it more dynamic like this.

In a free cell, say E1, enter:

=MATCH(9.999999E+307,A:A)

this tells us that last row a numeric entry is made in column A.

Then try:

=COUNT(1/FREQUENCY(IF(D2:INDEX(D:D,$E$1)= "Online Giving - Website",IF(B2:INDEX(B:B,$E$1)>=DATE(2008,1,1),IF(B2:INDEX(B:B,$E$1)<=DATE(2008,1,31),IF(A2:INDEX(A:A,$E$1)<>"",MATCH(A2:INDEX(A:A,$E$1),A2:INDEX(A:A,$E$1),0))))),ROW(A2:INDEX(A:A,$E$1))-ROW(A1)+1))

confirmed with CTRL+SHIFT+ENTER

this will adjust the ranges to cover from A2 to the last row indicated in E1 and hopefully make it a bit less resource intensive.