Hi,
I am trying to produce a count from the following sample data (actual
data over 2000 rows!)..driving me nuts..tried countif but not sure how
to implement it because of dates and multiple criteria

Region Date
North 01/01/05
South 04/05/06
West
East 09/04/01
North 04/03/04
South 11/01/03
West 15/03/01
East
North 14/12/04

Total by date

North 3
South 2
East 1
West 1

I can this can be done in a pivot table but I need a worksheet
function (trust me...if easier for a colleague!)
Can someone help?

Thanks

Chris

I'm assuming you want a count of, say, "East" that has a date beside it. In
this formula it only counts "East", but omits the ones in which the cell to
the right is blank.

=SUMPRODUCT(--(A1:A5000="East"),--(B1:B5000<""))

HTH,
Paul

wrote in message
ups.com...
Hi,
I am trying to produce a count from the following sample data (actual
data over 2000 rows!)..driving me nuts..tried countif but not sure how
to implement it because of dates and multiple criteria

Region Date
North 01/01/05
South 04/05/06
West
East 09/04/01
North 04/03/04
South 11/01/03
West 15/03/01
East
North 14/12/04

Total by date

North 3
South 2
East 1
West 1

I can this can be done in a pivot table but I need a worksheet
function (trust me...if easier for a colleague!)
Can someone help?

Thanks

Chris

I'm not exactly sure what you want, but try something like this

=SUMPRODUCT(--(A1:A2000="North"),--(isblank(B1:B2000)=False))

You *may* need to activate with CTRL SHIFT ENTER (but I don't think so)

" wrote:

Hi,
I am trying to produce a count from the following sample data (actual
data over 2000 rows!)..driving me nuts..tried countif but not sure how
to implement it because of dates and multiple criteria

Region Date
North 01/01/05
South 04/05/06
West
East 09/04/01
North 04/03/04
South 11/01/03
West 15/03/01
East
North 14/12/04

Total by date

North 3
South 2
East 1
West 1

I can this can be done in a pivot table but I need a worksheet
function (trust me...if easier for a colleague!)
Can someone help?

Thanks

Chris