How to check for a date range match in one column and then countthe values equal to in another
Try this:
=SUMPRODUCT((MONTH(A1:A1000)=1)*(YEAR(A1:A1000)=20 08)*(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)=C 2)*(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
On Jan 18, 9:07*am, Jon wrote:
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:A01/01/08<31/01/08,COUNTIF(B:B,"EC80"),)
although this doesn't work
Any help much appreciated!
Thanks Jon
|