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

[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

 

[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

 

[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.

 

[Pete_UK]

You're welcome, Jon - thanks for feeding back (so promptly).

Pete

 

[Jon]

You're welcome, Jon - thanks for feeding back (so promptly).

Pete





- Show quoted text -

Problem continued on
http://groups.google.co.uk/group/mi...hread/d83c7690be736153?hl=en#813c7b397cef6d88

 

[Pete_UK]

Answered at that thread.

Pete