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

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

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

How to check for a date range match in one column and then countthe values equal to in another
 

On 18 Jan, 09:20, Pete_UK wrote:
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- Hide quoted text -

- Show quoted text -



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

How to check for a date range match in one column and then countthe values equal to in another
 

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

Pete

On Jan 18, 9:28*am, Jon wrote:
On 18 Jan, 09:20, Pete_UK wrote:





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- Hide quoted text -

- Show quoted text -

Brilliant Pete, that appears to work!! Thanks for your help.- Hide quoted text -

- Show quoted text -

How to check for a date range match in one column and then countthe values equal to in another
 

On 18 Jan, 09:30, Pete_UK wrote:
You're welcome, Jon - thanks for feeding back (so promptly).

Pete

On Jan 18, 9:28*am, Jon wrote:



On 18 Jan, 09:20, Pete_UK wrote:

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- Hide quoted text -

- Show quoted text -

Brilliant Pete, that appears to work!! Thanks for your help.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Problem continued on
http://groups.google.co.uk/group/mic...3c7b397cef6d88

How to check for a date range match in one column and then countthe values equal to in another
 

Answered at that thread.

Pete

On Jan 18, 11:33*am, Jon wrote:

Problem continued onhttp://groups.google.co.uk/group/microsoft.public.excel.worksheet.fun...