How to count the date range with condition

[00itachi00]

Hi everyone can you please help me i want to count the date range with a conditional i'll explain below

on column A1:A50 is a date from August 1, 2012 - August 31 then, on B1:B50 there is a CODE 1,2,3,31,32
now i want to count for example how may CODE 1 is there in August 1, 2012 - August 31, 2012.
i know how to count date range by using sumproduct please help me..


alvin

[benishiryo]

hi alvin, since you mentioned that all dates are from Aug, why do you need to find Code 1 within Aug? COUNTIF will do.
=COUNTIF(B1:B50,1)

but if you really need it, try:
=SUMPRODUCT((A1:A50>=DATE(2012,8,1))*(A1:A50<=DATE(2012,8,31))*(B1:B50=1))

[00itachi00]

many thanks sir it solve my problem.

there is one more how about if text? for example on
a1: subbase b1: 100
a2: natural ground b2: 90
a3: formation level b3: 95
a4: subbase b4: 101
a5: selected fill b5: 90

i want to count with a condition goes like this IF a1:a5=subbase then it will count on b1:b5 the value greater than or equal to 100

thanks in advance

[benishiryo]

maybe something like:
=SUMPRODUCT((A1:A5="Sunbase")*(B1:B5>=100)*(B1:B5))

[Cutter]

Why not COUNTIFS() instead of SUMPRODUCT()?

=COUNTIFS(A1:A5,"Sunbase",B1:B5,">=100")

same for the original question

=COUNTIFS(A1:A50,">="&DATE(2012,8,1),A1:A50,"<="&DATE(2012,8,31),B1:B50,1)