If statement / Vlookup / Lookup
 

I'm not sure what function I need to use to accomplish what I'm trying to
do. I have a column that has a number of products listed:

Column A
Product 1
Product 2
Product 3
etc...

I have another tab that is a data dump from Microsoft Access. One column in
this dump includes dates and another column includes the product. I would
like to write a function that counts how many of "Product 1" were sold during
a time frame. The data dump includes everything from January 1st. But if I
only want to calculate the number of Product 1's that were sold in February
or March, how can I do this. I thought i could do =If(And("Date
Column"=03/01/05,"Date Column"<=03/31/05),countif(Product Column,"Product
1"),0)

Obviously that didn't work. Can anyone help?

Matt -

While this can be done in Excel (and one of the MVPs will probably whip up a
formula for you that can handle it in a breeze), this is SO EASILY done in
Access where the raw data resides that I'd suggest you do it there.

Alternatively, you can turn your raw data into a Pivot Table that groups on
products and months and counts the occurrences.

Duke


"Matt" wrote:


I'm not sure what function I need to use to accomplish what I'm trying to
do. I have a column that has a number of products listed:

Column A
Product 1
Product 2
Product 3
etc...

I have another tab that is a data dump from Microsoft Access. One column in
this dump includes dates and another column includes the product. I would
like to write a function that counts how many of "Product 1" were sold during
a time frame. The data dump includes everything from January 1st. But if I
only want to calculate the number of Product 1's that were sold in February
or March, how can I do this. I thought i could do =If(And("Date
Column"=03/01/05,"Date Column"<=03/31/05),countif(Product Column,"Product
1"),0)

Obviously that didn't work. Can anyone help?

For March, try:

=SUMPRODUCT((Sheet2!$A$1:$A$20=A1)*(TEXT(Sheet2!
$B$1:$B$20,"mmm")="Mar"))

where Access data is located on Sheet 2, with products in
col. A and dates in col. B.

Change "Mar" to whichever month you wish, using the 3
letter abbrev.

HTH
Jason
Atlanta, GA

-----Original Message-----

I'm not sure what function I need to use to accomplish
what I'm trying to
do. I have a column that has a number of products
listed:

Column A
Product 1
Product 2
Product 3
etc...

I have another tab that is a data dump from Microsoft
Access. One column in
this dump includes dates and another column includes the
product. I would
like to write a function that counts how many
of "Product 1" were sold during
a time frame. The data dump includes everything from
January 1st. But if I
only want to calculate the number of Product 1's that
were sold in February
or March, how can I do this. I thought i could do =If
(And("Date
Column"=03/01/05,"Date Column"<=03/31/05),countif
(Product Column,"Product
1"),0)

Obviously that didn't work. Can anyone help?
.