How to check for a date range match in one column and then count thevalues equal to in another

J

Jon

Hi,

Does anybody have the answer to this:

I have a worksheet with a "Date Column" and a "Model No. Column" I
want to be able to check the entire two columns using a formula and
have the worksheet count the no. of "model no.s" that are equal to X
on a month by month basis, e.g. Jan-08, Feb-08, Mar-08

e.g. all model numbers that are equal to EC80 during the month of
Jan..

I know I probably need to be using multiple IF statements but I'm at a
complete loss on how to do this...

maybe something like

where A = Date Column
where B = Model Column

=IF(A:A>01/01/08<31/01/08,COUNTIF(B:B,"EC80"),)

although this doesn't work

Any help much appreciated!

Thanks Jon
 
P

Pete_UK

Try this:

=SUMPRODUCT((MONTH(A1:A1000)=1)*(YEAR(A1:A1000)=2008)*(B1:B1000="Ec80"))

Or you could put the variables in separate cells and refer to them,
like this:

=SUMPRODUCT((MONTH(A1:A1000)=C1)*(YEAR(A1:A1000)=C2)*(B1:B1000=C3))

Put the month in C1, year in C2 and model number in C3.

You cannot use complete columns in your ranges (unless you have XL
2007).

Hope this helps.

Pete
 
J

Jon

Try this:

=SUMPRODUCT((MONTH(A1:A1000)=1)*(YEAR(A1:A1000)=2008)*(B1:B1000="Ec80"))

Or you could put the variables in separate cells and refer to them,
like this:

=SUMPRODUCT((MONTH(A1:A1000)=C1)*(YEAR(A1:A1000)=C2)*(B1:B1000=C3))

Put the month in C1, year in C2 and model number in C3.

You cannot use complete columns in your ranges (unless you have XL
2007).

Hope this helps.

Pete











- Show quoted text -



Brilliant Pete, that appears to work!! Thanks for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top