Counting Unique Numbers based on Date

**hanjimono** · 12-23-2010, 05:21 AM

I apologize if this has been covered, I tried searching and did not find anything.

I have a spreadsheet where column B is a four digit number and column H are different dates. What I'm trying to do is count all dates that are 90 days in the past and greater, but only for the unique numbers in column B.

The two pieces of code that I currently have are:

Please Login or Register  to view this content.

I just need a way to combine the two so it only counts column H if column B is a unique number. Any help with this would be greatly appreciated!

**zbor** · 12-23-2010, 05:36 AM

Hi.. Maybe something like this:

=SUMPRODUCT(--(COUNTIF(B3:B1000, B3:B1000)=1), --(H3:H1000<=(TODAY()-90)))

**hanjimono** · 12-23-2010, 05:41 AM

Worked perfectly! I was trying to mess around with the sumproduct function but couldn't quite get it to work. (May be a newbie question but..) In the instance what is the "--" doing?

**zbor** · 12-23-2010, 05:51 AM

in principle: -- converting TRUE/FALSE into 1/0 (numbers)

So when you have:

=SUMPRODUCT(--(A1:A10=1))

It will check numbers in a1 to A10 (for example)

=SUMPRODUCT(--({1, 2, 3, 4, 5, 6, 7, 8, 9, 1}=1))

And then it will compare it to =1

=SUMPRODUCT(--({TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, TRUE})

Now -- convert it to numbers (otherwise you can not calculate it)

=SUMPRODUCT({1,0,0,0,0,0,0,0,0,1}) which is 2