|
Count if dates meet certain criteria
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 |
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 |
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 |
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 |
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 |
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 |
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 |
Count if dates meet certain criteria
Hi Robert
You can try the below formula. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM((IF(A1:A1000,IF(A1:A100<C1,1,IF(B1:B1000,IF (B1:B100<C1,1,0)))))) If this post helps click Yes --------------- Jacob Skaria "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 |
Count if dates meet certain criteria
Try
=SUMPRODUCT(((ISNUMBER(A1:A10)*(A1:A10<$C$1)+(ISNU MBER(B1:B10)*(B1:B10<$C$1))0)*1)) The formula counts the cases where either the date in col A or B is less than C1. It ignores blank cells. Cheers, Joerg Mochikun "RobertK" wrote in message ... 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 |
Count if dates meet certain criteria
Thank You that did the trick.
-- Robert K "Joerg Mochikun" wrote: Try =SUMPRODUCT(((ISNUMBER(A1:A10)*(A1:A10<$C$1)+(ISNU MBER(B1:B10)*(B1:B10<$C$1))0)*1)) The formula counts the cases where either the date in col A or B is less than C1. It ignores blank cells. Cheers, Joerg Mochikun "RobertK" wrote in message ... 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 |
Count if dates meet certain criteria
Thank you, that did the trick.
-- Robert K "Jacob Skaria" wrote: Hi Robert You can try the below formula. Please note that this is an array formula. An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =SUM((IF(A1:A1000,IF(A1:A100<C1,1,IF(B1:B1000,IF (B1:B100<C1,1,0)))))) If this post helps click Yes --------------- Jacob Skaria "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 |
| All times are GMT +1. The time now is 01:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com