countif condition with date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

xtrmhyper - 01 Jun 2005 03:43 GMT

I Have A Column A,b, And C

A-----b--c
Jan-1 1 1
Jan-2 1 2
Jan-3 1 3
Jan-4 1 1
Jan-5 1 1
Jan-6 1 1

1st Problem:
    I Want To Count B=1 To C=1&2 And I Dont Want To Count B=1 & C=3
            Total Is 5
2nd Problem I Want To Count Base On The Covered Date
    I Want To Count From Jan 1 To Jan 4 Only B=1 To C=1&2
            Total Is 3

Tnx In Advance
God Speed!!!!

Signature

xtrmhyper

Reply to this Message

duane - 01 Jun 2005 03:52 GMT

I assume your data is in cells a1:c6

1st Problem:
I Want To Count B=1 To C=1&2 And I Dont Want To Count B=1 & C=3
Total Is 5

=sumproduct((b1:b6=1)*(c1:c6<3))

2nd Problem I Want To Count Base On The Covered Date
I Want To Count From Jan 1 To Jan 4 Only B=1 To C=1&2
Total Is 3

presumably your "dates" are in fact dates and not text - and are in the
year 2005

=sumproduct((a1:a6>=datevalue(1/1/2005))*(a1:a6<=datevalue(1/4/2005))*(b1:b6=1)*(c1:c6<3))

Signature

duane

Reply to this Message