countif condition with date

  • Thread starter Thread starter xtrmhyper
  • Start date Start date
X

xtrmhyper

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!!!!
 
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))
 
Back
Top