| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count if dates meet certain criteria
I am confused...
Please check the data and your query once again. Your Wordings:- In the example below 9 dates meet the criteria (8 in column A and 1 in column B) But if you see the example once again you will notice that two dates in A column (1-Mar-10 & 1-Jan-10) is greater that C1 Cell (i.e.) 16-Sep-09. So the Number of dates greater than C1 count is 2. Your Wordings:- In the example below 9 dates meet the criteria (8 in column A and 1 in column B) I dont know how you have calculated one date in Column B because all the dates are less that c1 cell. Please once again explain your query with clear picture. If you want to count the A&B Column values which is GREATER than C1 cell then use this formula: =COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1) If you want to count the A&B Column values which is LESS than C1 cell then use this formula: =COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "RobertK" wrote: I have a spreadsheet similar to this. I need to count the number of dates in column A that are less than C1. If the date in column A is greater than C1 than I want to count the date in column B if it is less than C1. If the date in column A & B are both less than C1 they should count as 1 and not 2. In the example below 9 dates meet the criteria (8 in column A and 1 in column B). Also the database length will vary and will contain blank cells after the last entry in column A. A B C 1/2/2009 3/2/2008 9/16/2009 3/2/2009 6/5/2008 6/5/2008 9/4/2008 3/1/2010 1/1/2010 4/1/2009 7/5/2009 9/15/2008 9/15/2009 12/4/2008 11/4/2008 2/12/2009 3/12/2010 -- Robert K |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if dates meet certain criteria
The countif formula works fine except that if the date in column A and the
date in column B are both less than C1, I don't want to count it each time. I only want to count it as one. Does this explanation help. -- Robert K "Ms-Exl-Learner" wrote: I am confused... Please check the data and your query once again. Your Wordings:- In the example below 9 dates meet the criteria (8 in column A and 1 in column B) But if you see the example once again you will notice that two dates in A column (1-Mar-10 & 1-Jan-10) is greater that C1 Cell (i.e.) 16-Sep-09. So the Number of dates greater than C1 count is 2. Your Wordings:- In the example below 9 dates meet the criteria (8 in column A and 1 in column B) I dont know how you have calculated one date in Column B because all the dates are less that c1 cell. Please once again explain your query with clear picture. If you want to count the A&B Column values which is GREATER than C1 cell then use this formula: =COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1) If you want to count the A&B Column values which is LESS than C1 cell then use this formula: =COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "RobertK" wrote: I have a spreadsheet similar to this. I need to count the number of dates in column A that are less than C1. If the date in column A is greater than C1 than I want to count the date in column B if it is less than C1. If the date in column A & B are both less than C1 they should count as 1 and not 2. In the example below 9 dates meet the criteria (8 in column A and 1 in column B). Also the database length will vary and will contain blank cells after the last entry in column A. A B C 1/2/2009 3/2/2008 9/16/2009 3/2/2009 6/5/2008 6/5/2008 9/4/2008 3/1/2010 1/1/2010 4/1/2009 7/5/2009 9/15/2008 9/15/2009 12/4/2008 11/4/2008 2/12/2009 3/12/2010 -- Robert K |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if dates meet certain criteria
Use this formula for finding the values in A&C Column which is GREATER than
C1 Cell:- =IF(COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1)<=0 ,"1",COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1 )) Use this for formula for finding the values in A&C Column which is LESS than C1 Cell:- =IF(COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1)<=0 ,"1",COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1 )) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "RobertK" wrote: The countif formula works fine except that if the date in column A and the date in column B are both less than C1, I don't want to count it each time. I only want to count it as one. Does this explanation help. -- Robert K "Ms-Exl-Learner" wrote: I am confused... Please check the data and your query once again. Your Wordings:- In the example below 9 dates meet the criteria (8 in column A and 1 in column B) But if you see the example once again you will notice that two dates in A column (1-Mar-10 & 1-Jan-10) is greater that C1 Cell (i.e.) 16-Sep-09. So the Number of dates greater than C1 count is 2. Your Wordings:- In the example below 9 dates meet the criteria (8 in column A and 1 in column B) I dont know how you have calculated one date in Column B because all the dates are less that c1 cell. Please once again explain your query with clear picture. If you want to count the A&B Column values which is GREATER than C1 cell then use this formula: =COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1) If you want to count the A&B Column values which is LESS than C1 cell then use this formula: =COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "RobertK" wrote: I have a spreadsheet similar to this. I need to count the number of dates in column A that are less than C1. If the date in column A is greater than C1 than I want to count the date in column B if it is less than C1. If the date in column A & B are both less than C1 they should count as 1 and not 2. In the example below 9 dates meet the criteria (8 in column A and 1 in column B). Also the database length will vary and will contain blank cells after the last entry in column A. A B C 1/2/2009 3/2/2008 9/16/2009 3/2/2009 6/5/2008 6/5/2008 9/4/2008 3/1/2010 1/1/2010 4/1/2009 7/5/2009 9/15/2008 9/15/2009 12/4/2008 11/4/2008 2/12/2009 3/12/2010 -- Robert K |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if dates meet certain criteria
Both the formulas can be reduced and the same is given below:-
Use this formula for finding the values in A&B Column which is GREATER than C1 Cell:- =IF(COUNTIF(A:B,""&$C$1)<=0,"1",COUNTIF(A:B,""&$ C$1)) Use this for formula for finding the values in A&B Column which is LESS than C1 Cell:- =IF(COUNTIF(A:B,"<"&$C$1)<=0,"1",COUNTIF(A:B,"<"&$ C$1)) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "Ms-Exl-Learner" wrote: Use this formula for finding the values in A&C Column which is GREATER than C1 Cell:- =IF(COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1)<=0 ,"1",COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1 )) Use this for formula for finding the values in A&C Column which is LESS than C1 Cell:- =IF(COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1)<=0 ,"1",COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1 )) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "RobertK" wrote: The countif formula works fine except that if the date in column A and the date in column B are both less than C1, I don't want to count it each time. I only want to count it as one. Does this explanation help. -- Robert K "Ms-Exl-Learner" wrote: I am confused... Please check the data and your query once again. Your Wordings:- In the example below 9 dates meet the criteria (8 in column A and 1 in column B) But if you see the example once again you will notice that two dates in A column (1-Mar-10 & 1-Jan-10) is greater that C1 Cell (i.e.) 16-Sep-09. So the Number of dates greater than C1 count is 2. Your Wordings:- In the example below 9 dates meet the criteria (8 in column A and 1 in column B) I dont know how you have calculated one date in Column B because all the dates are less that c1 cell. Please once again explain your query with clear picture. If you want to count the A&B Column values which is GREATER than C1 cell then use this formula: =COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1) If you want to count the A&B Column values which is LESS than C1 cell then use this formula: =COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "RobertK" wrote: I have a spreadsheet similar to this. I need to count the number of dates in column A that are less than C1. If the date in column A is greater than C1 than I want to count the date in column B if it is less than C1. If the date in column A & B are both less than C1 they should count as 1 and not 2. In the example below 9 dates meet the criteria (8 in column A and 1 in column B). Also the database length will vary and will contain blank cells after the last entry in column A. A B C 1/2/2009 3/2/2008 9/16/2009 3/2/2009 6/5/2008 6/5/2008 9/4/2008 3/1/2010 1/1/2010 4/1/2009 7/5/2009 9/15/2008 9/15/2009 12/4/2008 11/4/2008 2/12/2009 3/12/2010 -- Robert K |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if dates meet certain criteria
Sorry, I;m still not explaining this correctly. The formula you sent counts
all the dates in column A and B that are less than C1. I only want to count one date per row that is less than C1. A B C 1/2/2009 3/2/2009 9/15/09 3/2/2010 6/5/2008 In the example the first date in column A meets the criteria and both dates in column B meet the criteria. In this case the answer I'm looking for is 2. The 1/2/09 in column A and the 6/5/08 in column B. The 3/2/09 date is ignored in column B since the 1/2/09 date in column A has been counted. in other words only count one date per row that meets the criteria. Thanks, -- Robert K "Ms-Exl-Learner" wrote: Both the formulas can be reduced and the same is given below:- Use this formula for finding the values in A&B Column which is GREATER than C1 Cell:- =IF(COUNTIF(A:B,""&$C$1)<=0,"1",COUNTIF(A:B,""&$ C$1)) Use this for formula for finding the values in A&B Column which is LESS than C1 Cell:- =IF(COUNTIF(A:B,"<"&$C$1)<=0,"1",COUNTIF(A:B,"<"&$ C$1)) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "Ms-Exl-Learner" wrote: Use this formula for finding the values in A&C Column which is GREATER than C1 Cell:- =IF(COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1)<=0 ,"1",COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1 )) Use this for formula for finding the values in A&C Column which is LESS than C1 Cell:- =IF(COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1)<=0 ,"1",COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1 )) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "RobertK" wrote: The countif formula works fine except that if the date in column A and the date in column B are both less than C1, I don't want to count it each time. I only want to count it as one. Does this explanation help. -- Robert K "Ms-Exl-Learner" wrote: I am confused... Please check the data and your query once again. Your Wordings:- In the example below 9 dates meet the criteria (8 in column A and 1 in column B) But if you see the example once again you will notice that two dates in A column (1-Mar-10 & 1-Jan-10) is greater that C1 Cell (i.e.) 16-Sep-09. So the Number of dates greater than C1 count is 2. Your Wordings:- In the example below 9 dates meet the criteria (8 in column A and 1 in column B) I dont know how you have calculated one date in Column B because all the dates are less that c1 cell. Please once again explain your query with clear picture. If you want to count the A&B Column values which is GREATER than C1 cell then use this formula: =COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1) If you want to count the A&B Column values which is LESS than C1 cell then use this formula: =COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "RobertK" wrote: I have a spreadsheet similar to this. I need to count the number of dates in column A that are less than C1. If the date in column A is greater than C1 than I want to count the date in column B if it is less than C1. If the date in column A & B are both less than C1 they should count as 1 and not 2. In the example below 9 dates meet the criteria (8 in column A and 1 in column B). Also the database length will vary and will contain blank cells after the last entry in column A. A B C 1/2/2009 3/2/2008 9/16/2009 3/2/2009 6/5/2008 6/5/2008 9/4/2008 3/1/2010 1/1/2010 4/1/2009 7/5/2009 9/15/2008 9/15/2009 12/4/2008 11/4/2008 2/12/2009 3/12/2010 -- Robert K |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count if dates meet certain criteria
Robert,
Both (i.e.) A1 & B2 are less than c1 then you need to display the value as 2, If A1 or B2 is greater than C1 then what would you like to show? -- -------------------- (MS-Exl-Learner) -------------------- "RobertK" wrote: Sorry, I;m still not explaining this correctly. The formula you sent counts all the dates in column A and B that are less than C1. I only want to count one date per row that is less than C1. A B C 1/2/2009 3/2/2009 9/15/09 3/2/2010 6/5/2008 In the example the first date in column A meets the criteria and both dates in column B meet the criteria. In this case the answer I'm looking for is 2. The 1/2/09 in column A and the 6/5/08 in column B. The 3/2/09 date is ignored in column B since the 1/2/09 date in column A has been counted. in other words only count one date per row that meets the criteria. Thanks, -- Robert K "Ms-Exl-Learner" wrote: Both the formulas can be reduced and the same is given below:- Use this formula for finding the values in A&B Column which is GREATER than C1 Cell:- =IF(COUNTIF(A:B,""&$C$1)<=0,"1",COUNTIF(A:B,""&$ C$1)) Use this for formula for finding the values in A&B Column which is LESS than C1 Cell:- =IF(COUNTIF(A:B,"<"&$C$1)<=0,"1",COUNTIF(A:B,"<"&$ C$1)) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "Ms-Exl-Learner" wrote: Use this formula for finding the values in A&C Column which is GREATER than C1 Cell:- =IF(COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1)<=0 ,"1",COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1 )) Use this for formula for finding the values in A&C Column which is LESS than C1 Cell:- =IF(COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1)<=0 ,"1",COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1 )) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "RobertK" wrote: The countif formula works fine except that if the date in column A and the date in column B are both less than C1, I don't want to count it each time. I only want to count it as one. Does this explanation help. -- Robert K "Ms-Exl-Learner" wrote: I am confused... Please check the data and your query once again. Your Wordings:- In the example below 9 dates meet the criteria (8 in column A and 1 in column B) But if you see the example once again you will notice that two dates in A column (1-Mar-10 & 1-Jan-10) is greater that C1 Cell (i.e.) 16-Sep-09. So the Number of dates greater than C1 count is 2. Your Wordings:- In the example below 9 dates meet the criteria (8 in column A and 1 in column B) I dont know how you have calculated one date in Column B because all the dates are less that c1 cell. Please once again explain your query with clear picture. If you want to count the A&B Column values which is GREATER than C1 cell then use this formula: =COUNTIF(A:A,""&$C$1)+COUNTIF(B:B,""&$C$1) If you want to count the A&B Column values which is LESS than C1 cell then use this formula: =COUNTIF(A:A,"<"&$C$1)+COUNTIF(B:B,"<"&$C$1) If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "RobertK" wrote: I have a spreadsheet similar to this. I need to count the number of dates in column A that are less than C1. If the date in column A is greater than C1 than I want to count the date in column B if it is less than C1. If the date in column A & B are both less than C1 they should count as 1 and not 2. In the example below 9 dates meet the criteria (8 in column A and 1 in column B). Also the database length will vary and will contain blank cells after the last entry in column A. A B C 1/2/2009 3/2/2008 9/16/2009 3/2/2009 6/5/2008 6/5/2008 9/4/2008 3/1/2010 1/1/2010 4/1/2009 7/5/2009 9/15/2008 9/15/2009 12/4/2008 11/4/2008 2/12/2009 3/12/2010 -- Robert K |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count cells that meet 2 criteria | Excel Worksheet Functions | |||
| count rows that meet certain criteria | Excel Discussion (Misc queries) | |||
| Count Cells that meet Criteria | Excel Worksheet Functions | |||
| pulling back dates that meet certain criteria | Excel Worksheet Functions | |||
| how do I count the numbers of row that meet 2 criteria | Excel Worksheet Functions | |||
Hybrid Mode
