Need help with countif function

[annysjunkmail]

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

 

[PCLIVE]

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

 

[Guest]

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)

 

[annysjunkmail]

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)

Perfect!!!
obscure I know, but that's is what's wanted
Thanks very much

 

[annysjunkmail]

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

A bit out of discussion, hope you don't mind, but I am intrigued about
your solution and I was wondering if you shed some light
what is the effect of using two hyphens, i.e. "--"
does this convert a number of a positive?