Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells in a range per multiple criteria . . .
Hello,
This is probly a simple thing to do but I cant figure it out . . . I have a coulumn that is full of dates and I want to count how many cells fall within a certain range of dates. For example : A1 = 5/1/08 A2 = 5/5/08 A3 = 5/12/08 A4 = 5/14/08 A5 = 5/25/08 I want to how many cells in this range are earlier than or equal to 5/25/08 but later than or equal to 5/12/08. Should be 3 but what would the formula look like to calculate that? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells in a range per multiple criteria . . .
Try this:
C1 = 5/12/2008 D1 = 5/25/2008 =COUNTIF(A1:A5,"="&C1)-COUNTIF(A1:A5,""&D1) Format as GENERAL or NUMBER -- Biff Microsoft Excel MVP "Dano" wrote in message ... Hello, This is probly a simple thing to do but I cant figure it out . . . I have a coulumn that is full of dates and I want to count how many cells fall within a certain range of dates. For example : A1 = 5/1/08 A2 = 5/5/08 A3 = 5/12/08 A4 = 5/14/08 A5 = 5/25/08 I want to how many cells in this range are earlier than or equal to 5/25/08 but later than or equal to 5/12/08. Should be 3 but what would the formula look like to calculate that? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells in a range per multiple criteria . . .
Hi Biff,
Just jumping in... I tried your formula - it works, of course :) Could you please explain to me why the operators need to be in double quotes, and why we need the & thingy? Regards - Dave. "T. Valko" wrote: Try this: C1 = 5/12/2008 D1 = 5/25/2008 =COUNTIF(A1:A5,"="&C1)-COUNTIF(A1:A5,""&D1) Format as GENERAL or NUMBER -- Biff Microsoft Excel MVP |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells in a range per multiple criteria . . .
"Why" is a hard question to answer in this case!
I don't know "why" other than to say that's how the programmers that developed the formula parser wrote it to work. When using a comparison operator and referring to a reference (which might also be another function) You have to concatenate the operator to the reference (with certain functions, COUNTIF being one of those): =COUNTIF(A1:A5,"="&C1) =COUNTIF(A1:A5,"="&DATE(2008,5,12)) If you tried this: =COUNTIF(A1:A5,"=C1") Then it evaluates "=C1" as the literal TEXT string =C1 But, if the comparison criteria is a hard coded constant you just enclose both the operator and criteria in quotes (although concatenation will still work): =COUNTIF(A1:A5,"=10") =COUNTIF(A1:A5,"="&10) When testing for equality then no operator is required but it still works if you do include it: =COUNTIF(A1:A10,10) =COUNTIF(A1:A10,"=10") =COUNTIF(A1:A10,"="&10) These are nuances of Excel that you learn and get used to over time! -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi Biff, Just jumping in... I tried your formula - it works, of course :) Could you please explain to me why the operators need to be in double quotes, and why we need the & thingy? Regards - Dave. "T. Valko" wrote: Try this: C1 = 5/12/2008 D1 = 5/25/2008 =COUNTIF(A1:A5,"="&C1)-COUNTIF(A1:A5,""&D1) Format as GENERAL or NUMBER -- Biff Microsoft Excel MVP |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells in a range per multiple criteria . . .
Hi Biff,
Thanks for your detailed reply. Countif is amazingly useful, and it's good to know its "nuances" Regards - Dave. "T. Valko" wrote: "Why" is a hard question to answer in this case! I don't know "why" other than to say that's how the programmers that developed the formula parser wrote it to work. When using a comparison operator and referring to a reference (which might also be another function) You have to concatenate the operator to the reference (with certain functions, COUNTIF being one of those): =COUNTIF(A1:A5,"="&C1) =COUNTIF(A1:A5,"="&DATE(2008,5,12)) If you tried this: =COUNTIF(A1:A5,"=C1") Then it evaluates "=C1" as the literal TEXT string =C1 But, if the comparison criteria is a hard coded constant you just enclose both the operator and criteria in quotes (although concatenation will still work): =COUNTIF(A1:A5,"=10") =COUNTIF(A1:A5,"="&10) When testing for equality then no operator is required but it still works if you do include it: =COUNTIF(A1:A10,10) =COUNTIF(A1:A10,"=10") =COUNTIF(A1:A10,"="&10) These are nuances of Excel that you learn and get used to over time! -- Biff Microsoft Excel MVP |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells in a range per multiple criteria . . .
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi Biff, Thanks for your detailed reply. Countif is amazingly useful, and it's good to know its "nuances" Regards - Dave. "T. Valko" wrote: "Why" is a hard question to answer in this case! I don't know "why" other than to say that's how the programmers that developed the formula parser wrote it to work. When using a comparison operator and referring to a reference (which might also be another function) You have to concatenate the operator to the reference (with certain functions, COUNTIF being one of those): =COUNTIF(A1:A5,"="&C1) =COUNTIF(A1:A5,"="&DATE(2008,5,12)) If you tried this: =COUNTIF(A1:A5,"=C1") Then it evaluates "=C1" as the literal TEXT string =C1 But, if the comparison criteria is a hard coded constant you just enclose both the operator and criteria in quotes (although concatenation will still work): =COUNTIF(A1:A5,"=10") =COUNTIF(A1:A5,"="&10) When testing for equality then no operator is required but it still works if you do include it: =COUNTIF(A1:A10,10) =COUNTIF(A1:A10,"=10") =COUNTIF(A1:A10,"="&10) These are nuances of Excel that you learn and get used to over time! -- Biff Microsoft Excel MVP |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells in a range per multiple criteria . . .
Fellas thanks a lot that will help me out!
Dan "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi Biff, Thanks for your detailed reply. Countif is amazingly useful, and it's good to know its "nuances" Regards - Dave. "T. Valko" wrote: "Why" is a hard question to answer in this case! I don't know "why" other than to say that's how the programmers that developed the formula parser wrote it to work. When using a comparison operator and referring to a reference (which might also be another function) You have to concatenate the operator to the reference (with certain functions, COUNTIF being one of those): =COUNTIF(A1:A5,"="&C1) =COUNTIF(A1:A5,"="&DATE(2008,5,12)) If you tried this: =COUNTIF(A1:A5,"=C1") Then it evaluates "=C1" as the literal TEXT string =C1 But, if the comparison criteria is a hard coded constant you just enclose both the operator and criteria in quotes (although concatenation will still work): =COUNTIF(A1:A5,"=10") =COUNTIF(A1:A5,"="&10) When testing for equality then no operator is required but it still works if you do include it: =COUNTIF(A1:A10,10) =COUNTIF(A1:A10,"=10") =COUNTIF(A1:A10,"="&10) These are nuances of Excel that you learn and get used to over time! -- Biff Microsoft Excel MVP |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells in a range per multiple criteria . . .
Try this:
G1 = 5/12/2008 H1 = 5/25/2008 I1 = Done =SUMPRODUCT(--(A1:A5=G1),--(A1:A5<=H1),--(E1:E5=I1)) -- Biff Microsoft Excel MVP "Dano" wrote in message ... Ok I have another question with this same thing here . . . Say the dates in A1 thru A5 remain the same. I add the following column : E1 = "Done" E2 = "Open" E3 = "Open" E4 = "Done" E5 = "Done" and I want to know how many cells in this range are earlier than or equal to 5/25/08 but later than or equal to 5/12/08 and only count the cells that have "Done" in column E? Should be 2 but how would you constuct a formula for that? Thanks a lot for your help. Dan "Dano" wrote: Fellas thanks a lot that will help me out! Dan "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi Biff, Thanks for your detailed reply. Countif is amazingly useful, and it's good to know its "nuances" Regards - Dave. "T. Valko" wrote: "Why" is a hard question to answer in this case! I don't know "why" other than to say that's how the programmers that developed the formula parser wrote it to work. When using a comparison operator and referring to a reference (which might also be another function) You have to concatenate the operator to the reference (with certain functions, COUNTIF being one of those): =COUNTIF(A1:A5,"="&C1) =COUNTIF(A1:A5,"="&DATE(2008,5,12)) If you tried this: =COUNTIF(A1:A5,"=C1") Then it evaluates "=C1" as the literal TEXT string =C1 But, if the comparison criteria is a hard coded constant you just enclose both the operator and criteria in quotes (although concatenation will still work): =COUNTIF(A1:A5,"=10") =COUNTIF(A1:A5,"="&10) When testing for equality then no operator is required but it still works if you do include it: =COUNTIF(A1:A10,10) =COUNTIF(A1:A10,"=10") =COUNTIF(A1:A10,"="&10) These are nuances of Excel that you learn and get used to over time! -- Biff Microsoft Excel MVP |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells in a range per multiple criteria . . .
Alright that works perfect!
Thanks again! "T. Valko" wrote: Try this: G1 = 5/12/2008 H1 = 5/25/2008 I1 = Done =SUMPRODUCT(--(A1:A5=G1),--(A1:A5<=H1),--(E1:E5=I1)) -- Biff Microsoft Excel MVP "Dano" wrote in message ... Ok I have another question with this same thing here . . . Say the dates in A1 thru A5 remain the same. I add the following column : E1 = "Done" E2 = "Open" E3 = "Open" E4 = "Done" E5 = "Done" and I want to know how many cells in this range are earlier than or equal to 5/25/08 but later than or equal to 5/12/08 and only count the cells that have "Done" in column E? Should be 2 but how would you constuct a formula for that? Thanks a lot for your help. Dan "Dano" wrote: Fellas thanks a lot that will help me out! Dan "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi Biff, Thanks for your detailed reply. Countif is amazingly useful, and it's good to know its "nuances" Regards - Dave. "T. Valko" wrote: "Why" is a hard question to answer in this case! I don't know "why" other than to say that's how the programmers that developed the formula parser wrote it to work. When using a comparison operator and referring to a reference (which might also be another function) You have to concatenate the operator to the reference (with certain functions, COUNTIF being one of those): =COUNTIF(A1:A5,"="&C1) =COUNTIF(A1:A5,"="&DATE(2008,5,12)) If you tried this: =COUNTIF(A1:A5,"=C1") Then it evaluates "=C1" as the literal TEXT string =C1 But, if the comparison criteria is a hard coded constant you just enclose both the operator and criteria in quotes (although concatenation will still work): =COUNTIF(A1:A5,"=10") =COUNTIF(A1:A5,"="&10) When testing for equality then no operator is required but it still works if you do include it: =COUNTIF(A1:A10,10) =COUNTIF(A1:A10,"=10") =COUNTIF(A1:A10,"="&10) These are nuances of Excel that you learn and get used to over time! -- Biff Microsoft Excel MVP |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting cells in a range per multiple criteria . . .
You're welcome!
-- Biff Microsoft Excel MVP "Dano" wrote in message ... Alright that works perfect! Thanks again! "T. Valko" wrote: Try this: G1 = 5/12/2008 H1 = 5/25/2008 I1 = Done =SUMPRODUCT(--(A1:A5=G1),--(A1:A5<=H1),--(E1:E5=I1)) -- Biff Microsoft Excel MVP "Dano" wrote in message ... Ok I have another question with this same thing here . . . Say the dates in A1 thru A5 remain the same. I add the following column : E1 = "Done" E2 = "Open" E3 = "Open" E4 = "Done" E5 = "Done" and I want to know how many cells in this range are earlier than or equal to 5/25/08 but later than or equal to 5/12/08 and only count the cells that have "Done" in column E? Should be 2 but how would you constuct a formula for that? Thanks a lot for your help. Dan "Dano" wrote: Fellas thanks a lot that will help me out! Dan "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Dave" wrote in message ... Hi Biff, Thanks for your detailed reply. Countif is amazingly useful, and it's good to know its "nuances" Regards - Dave. "T. Valko" wrote: "Why" is a hard question to answer in this case! I don't know "why" other than to say that's how the programmers that developed the formula parser wrote it to work. When using a comparison operator and referring to a reference (which might also be another function) You have to concatenate the operator to the reference (with certain functions, COUNTIF being one of those): =COUNTIF(A1:A5,"="&C1) =COUNTIF(A1:A5,"="&DATE(2008,5,12)) If you tried this: =COUNTIF(A1:A5,"=C1") Then it evaluates "=C1" as the literal TEXT string =C1 But, if the comparison criteria is a hard coded constant you just enclose both the operator and criteria in quotes (although concatenation will still work): =COUNTIF(A1:A5,"=10") =COUNTIF(A1:A5,"="&10) When testing for equality then no operator is required but it still works if you do include it: =COUNTIF(A1:A10,10) =COUNTIF(A1:A10,"=10") =COUNTIF(A1:A10,"="&10) These are nuances of Excel that you learn and get used to over time! -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting cells using multiple criteria | Excel Worksheet Functions | |||
Counting from one range to another range, multiple criteria | Excel Discussion (Misc queries) | |||
Counting multiple cells using a criteria | Excel Discussion (Misc queries) | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |