count if using columns not range of cells

[Opal]

Hi,
Im trying to generate a count that will tell me how many of 'x' are in
column B only if column A's data are within a date range eg for the month of
June.

A B
16-Jun-08 Apple
16-Jun-08 Pear
30-May-08 Apple
18-Jun-08 Orange
16-Jun-08 Apple
12-Apr-08 Pear

Thanks

 

[Teethless mama]

=SUMPRODUCT(--(TEXT(A1:A100,"mmmyy")="Jun08"),--(B1:B100="Apple"))

 

[Bob Phillips]

=SUMPRODUCT(--(TEXT(A1:A100,"yyyymmm")="2008Jun"),--(B1:B100="Apple"))

 

[Max]

One way, assuming data in cols A and B expected within rows 2 to 10
In say, C2, copied down:
=IF(A2="","",SUMPRODUCT((TEXT(A$2:A$10,"mmmyy")="Jun08")*(B$2:B$10=B2)))
Adapt ranges to suit

 

[Opal]

Thanks so much "Teethless Mama"!

Only thing was my comp wasnt recognising ur commas. I had to change them to
semicolons. Thanks again!

 

[Bigfoot17]

And what if you wanted those between April 15 and May 15?

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A100>=--"2008-04-15"),--(A1:A100<--"2008-05-15"),--(B1:B100="Apple"))--__________________________________HTHBob"Bigfoot17" <[email protected]> wrote in messageAnd what if you wanted those between April 15 and May 15?>> "Bob Phillips" wrote:>>> =SUMPRODUCT(--(TEXT(A1:A100,"yyyymmm")="2008Jun"),--(B1:B100="Apple"))>>>> -->> __________________________________>> HTH>>>> Bob>>>> "Opal" <[email protected]> wrote in message>> > Hi,>> > Im trying to generate a count that will tell me how many of 'x' are in>> > column B only if column A's data are within a date range eg for themonth>> > of>> > June.>> >>> > A B>> > 16-Jun-08 Apple>> > 16-Jun-08 Pear>> > 30-May-08 Apple>> > 18-Jun-08 Orange>> > 16-Jun-08 Apple>> > 12-Apr-08 Pear>> >>> > Thanks>> > -->> > Opal>>>>>>

 

[Bob Phillips]

Whoa! What happened there

=SUMPRODUCT(--(A1:A100>=--"2008-04-15"),--(A1:A100<--"2008-05-15"),--(B1:B100="Apple"))