count if using columns not range of cells

's Computer Specifications

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
--
Opal

's Computer Specifications

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

"Opal" wrote:

> 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
> --
> Opal

's Computer Specifications

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

--
__________________________________
HTH

Bob

"Opal" <(E-Mail Removed)> wrote in message
news:3AFCBB83-EDDA-436E-B1B5-(E-Mail Removed)...
> 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
> --
> Opal

's Computer Specifications

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Opal" wrote:
> 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
> --
> Opal

's Computer Specifications

Thanks so much "Teethless Mama"!

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

"Teethless mama" wrote:

> =SUMPRODUCT(--(TEXT(A1:A100,"mmmyy")="Jun08"),--(B1:B100="Apple"))
>
>
> "Opal" wrote:
>
> > 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
> > --
> > Opal

's Computer Specifications

And 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" <(E-Mail Removed)> wrote in message
> news:3AFCBB83-EDDA-436E-B1B5-(E-Mail Removed)...
> > 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
> > --
> > Opal
>
>
>

's Computer Specifications

=SUMPRODUCT(--(A1:A100>=--"2008-04-15"),--(A1:A100<--"2008-05-15"),--(B1:B100="Apple"))--__________________________________HTHBob"Bigfoot17" <(E-Mail Removed)> wrote in messagenews:9F6E53D6-8DB7-46CF-B3A6-(E-Mail Removed)...> And 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" <(E-Mail Removed)> wrote in message>> news:3AFCBB83-EDDA-436E-B1B5-(E-Mail Removed)...>> > 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>>>>>>

's Computer Specifications

Whoa! What happened there

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

--
__________________________________
HTH

Bob

"Bob Phillips" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> =SUMPRODUCT(--(A1:A100>=--"2008-04-15"),--(A1:A100<--"2008-05-15"),--(B1:B100="Apple"))--__________________________________HTHBob"Bigfoot17"
> <(E-Mail Removed)> wrote in
> messagenews:9F6E53D6-8DB7-46CF-B3A6-(E-Mail Removed)...> And
> 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"
> <(E-Mail Removed)> wrote in message>>
> news:3AFCBB83-EDDA-436E-B1B5-(E-Mail Removed)...>> > 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>>>>>>
>

Thread Tools
Show Printable Version Email this Page
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I count a range of cells from another columns criteria	angiec50	Microsoft Excel Worksheet Functions	6	25th Nov 2008 04:34 PM
Count Empty Cells in Range After Cells with Data	=?Utf-8?B?RGF2aWQ=?=	Microsoft Excel Programming	16	17th Sep 2006 03:03 PM
Count cells in one range based on parameters in another range	=?Utf-8?B?ZGF2ZSByb3Ro?=	Microsoft Excel Worksheet Functions	2	29th Mar 2005 05:33 PM
Range("Weekending").Cells.Columns.Count	Carl Brehm	Microsoft Excel Programming	4	16th Feb 2005 11:20 AM
A calculation to count all cells ='D' where the offset cells in range have dates <= todays date	AlanN	Microsoft Excel Misc	2	29th Jan 2004 03:57 PM