[Excel] Count and Null Dates

[GlasgowSwan]

A B C D
1 Type Colour Sold On
2 Car Red 01/01/2008
3 Car Red
4 Bus Red 01/01/2008
5 Car Green
6 Bus Green
7 Car Red 10/01/2008
8 09/01/2008

I would like to count all of red cars which had not been sold before the date entered in D8 and still have not been sold (blank).

The result should be 2.

 

[SkipVought]

AutoFilter does not handle mixed values well. Mixed values in this case, is dates and blanks.

I'd put a formula in column D, that used ISBLANK and also tests fro your date criteria...
[tt]
=IF(ISBLANK(C2),1,IF(C2<$D$8,1,0))
[/tt]
Then filter on 1s in column D.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GlennUK]

Don't you mean or here:

I would like to count all of red cars which had not been sold before the date entered in D8 and still have not been sold (blank).

... but apart from that, this formula should do it, as empty cells are counted as less than the specified date:

=SUMPRODUCT((A2:A7="Car")*(B2:B7="Red")*(C2:C7<=C8))

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AnotherHiggins]

Did Glenn's formula or the formula I posted in thread68-1481026 work for you?

Do you understand how to define criteria now?



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GlasgowSwan]

Thanks for the help so far.

The first part of the formula is fine however the formula posted by Glenn lists all which had been sold before the date and all blanks.

I need all after the date and blanks.

 

[SkipVought]

"I need all after the date and blanks."

Hmmmmm?

Can't YOU change the criteria in the formula that was given to you???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GlasgowSwan]

Skip

I have changed the criteria. However if I change < to > it does not include the blank cells

 

[SkipVought]

You must must use Two

One for =0 and one for >D8

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Sorry, TWO separate formula

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

=SUMPRODUCT((A2:A7="Car") * (B2:B7="Red") * (C2:C7>=C8)) [!]+[/!] SUMPRODUCT((A2:A7="Car") * (B2:B7="Red") * (C2:C7=""))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GlasgowSwan]

Many thanks this worked