Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a range of cells in a column equal to another cell | New Users to Excel | |||
Count number of values equal to MAX of a column | Excel Discussion (Misc queries) | |||
2 column lookup - match to date range | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Count cells based on date range in another column | New Users to Excel |