| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
CountIf Function - require range of criterea
Excel 2003
Looking for a function that will handle: Count AND a range of criterea Something like: =COUNTIF(X58:X73,"23 & <31") =COUNTIF(X58:X73,"30,<58") =COUNTIF(X58:X73,"56,<73") (but this doesn't work) any ideas? Thanks -- John F. Scholten |
|
#2
|
|||
|
|||
|
A couple of ways
=COUNTIF(X58:X73,"23")-COUNTIF(X58:X73,"=31") =SUMPRODUCT(--(X58:X7323),--(X58:X73<31)) then adapt the rest of the conditions using the same technique Regards, Peo Sjoblom "John F" wrote: Excel 2003 Looking for a function that will handle: Count AND a range of criterea Something like: =COUNTIF(X58:X73,"23 & <31") =COUNTIF(X58:X73,"30,<58") =COUNTIF(X58:X73,"56,<73") (but this doesn't work) any ideas? Thanks -- John F. Scholten |
|
#3
|
|||
|
|||
|
Sounds like you want SUMPRODUCT.
=SUMPRODUCT((X58:X7323)*(X58:X73<31)) Check this out: http://www.xldynamic.com/source/xld.SUMPRODUCT.html "John F" wrote: Excel 2003 Looking for a function that will handle: Count AND a range of criterea Something like: =COUNTIF(X58:X73,"23 & <31") =COUNTIF(X58:X73,"30,<58") =COUNTIF(X58:X73,"56,<73") (but this doesn't work) any ideas? Thanks -- John F. Scholten |
|
#4
|
|||
|
|||
|
A set of 2 critera that can be taken together as an interval...
=COUNTIF(X58:X73,""&Y2)-COUNTIF(X58:X73,"="&Z2) where Y2 <= Z2 like with 23 and 31, repectively, we get a count of numbers that fall in the interval specified by " 23 and < 31". John F wrote: Excel 2003 Looking for a function that will handle: Count AND a range of criterea Something like: =COUNTIF(X58:X73,"23 & <31") =COUNTIF(X58:X73,"30,<58") =COUNTIF(X58:X73,"56,<73") (but this doesn't work) any ideas? Thanks |
|
#5
|
|||
|
|||
|
Thanks ALL:
I'll try each method so it will hopefully drill in my head somewhere. I'll have to come back after supper to try them out. The prompt response is greatly appreciated. John F. "Aladin Akyurek" wrote: A set of 2 critera that can be taken together as an interval... =COUNTIF(X58:X73,""&Y2)-COUNTIF(X58:X73,"="&Z2) where Y2 <= Z2 like with 23 and 31, repectively, we get a count of numbers that fall in the interval specified by " 23 and < 31". John F wrote: Excel 2003 Looking for a function that will handle: Count AND a range of criterea Something like: =COUNTIF(X58:X73,"23 & <31") =COUNTIF(X58:X73,"30,<58") =COUNTIF(X58:X73,"56,<73") (but this doesn't work) any ideas? Thanks |
|
#6
|
|||
|
|||
|
I'm having trouble implementing the various formulas I'm seeing posted. I
think I want the same thing many others are asking for but must be doing something wrong: I used several including: =COUNTIF(A1:A5,""&0)-COUNTIF(A1:A5,"<"&10) RANGE CONTENTS: 2 | 5 | 17 | 6 | 29 So, I would expect my formula result to equal 3. How do I acheive this? Thanks in advance! SJP~ "John F" wrote: Thanks ALL: I'll try each method so it will hopefully drill in my head somewhere. I'll have to come back after supper to try them out. The prompt response is greatly appreciated. John F. "Aladin Akyurek" wrote: A set of 2 critera that can be taken together as an interval... =COUNTIF(X58:X73,""&Y2)-COUNTIF(X58:X73,"="&Z2) where Y2 <= Z2 like with 23 and 31, repectively, we get a count of numbers that fall in the interval specified by " 23 and < 31". John F wrote: Excel 2003 Looking for a function that will handle: Count AND a range of criterea Something like: =COUNTIF(X58:X73,"23 & <31") =COUNTIF(X58:X73,"30,<58") =COUNTIF(X58:X73,"56,<73") (but this doesn't work) any ideas? Thanks |
|
#7
|
|||
|
|||
|
Hi
Try: =COUNTIF(A1:A5,""&0)-COUNTIF(A1:A5,""&10) You want a count of all and then take off those that are bigger than 10, I guess. -- Andy. "SP" wrote in message ... I'm having trouble implementing the various formulas I'm seeing posted. I think I want the same thing many others are asking for but must be doing something wrong: I used several including: =COUNTIF(A1:A5,""&0)-COUNTIF(A1:A5,"<"&10) RANGE CONTENTS: 2 | 5 | 17 | 6 | 29 So, I would expect my formula result to equal 3. How do I acheive this? Thanks in advance! SJP~ "John F" wrote: Thanks ALL: I'll try each method so it will hopefully drill in my head somewhere. I'll have to come back after supper to try them out. The prompt response is greatly appreciated. John F. "Aladin Akyurek" wrote: A set of 2 critera that can be taken together as an interval... =COUNTIF(X58:X73,""&Y2)-COUNTIF(X58:X73,"="&Z2) where Y2 <= Z2 like with 23 and 31, repectively, we get a count of numbers that fall in the interval specified by " 23 and < 31". John F wrote: Excel 2003 Looking for a function that will handle: Count AND a range of criterea Something like: =COUNTIF(X58:X73,"23 & <31") =COUNTIF(X58:X73,"30,<58") =COUNTIF(X58:X73,"56,<73") (but this doesn't work) any ideas? Thanks |
|
#8
|
|||
|
|||
|
Ahhhhh yes!! Thank you...my little brain just was simply not thinking in that
way!! It seems so simple when I look at this now but I was determined to try to look in-between rather than subtract from a total! I may be back for more help later ;o) Thanks again, Steve "Andy B" wrote: Hi Try: =COUNTIF(A1:A5,""&0)-COUNTIF(A1:A5,""&10) You want a count of all and then take off those that are bigger than 10, I guess. -- Andy. "SP" wrote in message ... I'm having trouble implementing the various formulas I'm seeing posted. I think I want the same thing many others are asking for but must be doing something wrong: I used several including: =COUNTIF(A1:A5,""&0)-COUNTIF(A1:A5,"<"&10) RANGE CONTENTS: 2 | 5 | 17 | 6 | 29 So, I would expect my formula result to equal 3. How do I acheive this? Thanks in advance! SJP~ "John F" wrote: Thanks ALL: I'll try each method so it will hopefully drill in my head somewhere. I'll have to come back after supper to try them out. The prompt response is greatly appreciated. John F. "Aladin Akyurek" wrote: A set of 2 critera that can be taken together as an interval... =COUNTIF(X58:X73,""&Y2)-COUNTIF(X58:X73,"="&Z2) where Y2 <= Z2 like with 23 and 31, repectively, we get a count of numbers that fall in the interval specified by " 23 and < 31". John F wrote: Excel 2003 Looking for a function that will handle: Count AND a range of criterea Something like: =COUNTIF(X58:X73,"23 & <31") =COUNTIF(X58:X73,"30,<58") =COUNTIF(X58:X73,"56,<73") (but this doesn't work) any ideas? Thanks |
|
#9
|
|||
|
|||
|
Glad to help! Thanks for the feedback. In future, it may help you to know
that you can select parts of the formula, in the formula bar, and hit F9. Excel will then show you what the result of that bit of the formula equates to. To exit, hit Esc. -- Andy. "SP" wrote in message ... Ahhhhh yes!! Thank you...my little brain just was simply not thinking in that way!! It seems so simple when I look at this now but I was determined to try to look in-between rather than subtract from a total! I may be back for more help later ;o) Thanks again, Steve "Andy B" wrote: Hi Try: =COUNTIF(A1:A5,""&0)-COUNTIF(A1:A5,""&10) You want a count of all and then take off those that are bigger than 10, I guess. -- Andy. "SP" wrote in message ... I'm having trouble implementing the various formulas I'm seeing posted. I think I want the same thing many others are asking for but must be doing something wrong: I used several including: =COUNTIF(A1:A5,""&0)-COUNTIF(A1:A5,"<"&10) RANGE CONTENTS: 2 | 5 | 17 | 6 | 29 So, I would expect my formula result to equal 3. How do I acheive this? Thanks in advance! SJP~ "John F" wrote: Thanks ALL: I'll try each method so it will hopefully drill in my head somewhere. I'll have to come back after supper to try them out. The prompt response is greatly appreciated. John F. "Aladin Akyurek" wrote: A set of 2 critera that can be taken together as an interval... =COUNTIF(X58:X73,""&Y2)-COUNTIF(X58:X73,"="&Z2) where Y2 <= Z2 like with 23 and 31, repectively, we get a count of numbers that fall in the interval specified by " 23 and < 31". John F wrote: Excel 2003 Looking for a function that will handle: Count AND a range of criterea Something like: =COUNTIF(X58:X73,"23 & <31") =COUNTIF(X58:X73,"30,<58") =COUNTIF(X58:X73,"56,<73") (but this doesn't work) any ideas? Thanks |
|
#10
|
|||
|
|||
|
That is helpful...I didn't know that!
Also, I'm back for more help... Now I was hoping to capture and sum cells that fall within the criteria found from the previous formula results. For instance, once it has been determined that there are 3 cells that were returned by the formula below, I would like to capture the value in a cell on the same row (a few columns over) that has a cost there and I want to sum the costs of those 3. I hope I'm being clear...if not let me know and I will try to do better. Thanks! "Andy B" wrote: Glad to help! Thanks for the feedback. In future, it may help you to know that you can select parts of the formula, in the formula bar, and hit F9. Excel will then show you what the result of that bit of the formula equates to. To exit, hit Esc. -- Andy. "SP" wrote in message ... Ahhhhh yes!! Thank you...my little brain just was simply not thinking in that way!! It seems so simple when I look at this now but I was determined to try to look in-between rather than subtract from a total! I may be back for more help later ;o) Thanks again, Steve "Andy B" wrote: Hi Try: =COUNTIF(A1:A5,""&0)-COUNTIF(A1:A5,""&10) You want a count of all and then take off those that are bigger than 10, I guess. -- Andy. "SP" wrote in message ... I'm having trouble implementing the various formulas I'm seeing posted. I think I want the same thing many others are asking for but must be doing something wrong: I used several including: =COUNTIF(A1:A5,""&0)-COUNTIF(A1:A5,"<"&10) RANGE CONTENTS: 2 | 5 | 17 | 6 | 29 So, I would expect my formula result to equal 3. How do I acheive this? Thanks in advance! SJP~ "John F" wrote: Thanks ALL: I'll try each method so it will hopefully drill in my head somewhere. I'll have to come back after supper to try them out. The prompt response is greatly appreciated. John F. "Aladin Akyurek" wrote: A set of 2 critera that can be taken together as an interval... =COUNTIF(X58:X73,""&Y2)-COUNTIF(X58:X73,"="&Z2) where Y2 <= Z2 like with 23 and 31, repectively, we get a count of numbers that fall in the interval specified by " 23 and < 31". John F wrote: Excel 2003 Looking for a function that will handle: Count AND a range of criterea Something like: =COUNTIF(X58:X73,"23 & <31") =COUNTIF(X58:X73,"30,<58") =COUNTIF(X58:X73,"56,<73") (but this doesn't work) any ideas? Thanks |
|
#11
|
|||
|
|||
|
Hi!
You need a different function to do that: So, what you want to do is sum values in another column that correspond to values in column A that are 0 and <10. If that's what you want: =SUMPRODUCT(--(A1:A100),--(A1:A10<10),C1:C10) Biff "SP" wrote in message ... That is helpful...I didn't know that! Also, I'm back for more help... Now I was hoping to capture and sum cells that fall within the criteria found from the previous formula results. For instance, once it has been determined that there are 3 cells that were returned by the formula below, I would like to capture the value in a cell on the same row (a few columns over) that has a cost there and I want to sum the costs of those 3. I hope I'm being clear...if not let me know and I will try to do better. Thanks! "Andy B" wrote: Glad to help! Thanks for the feedback. In future, it may help you to know that you can select parts of the formula, in the formula bar, and hit F9. Excel will then show you what the result of that bit of the formula equates to. To exit, hit Esc. -- Andy. "SP" wrote in message ... Ahhhhh yes!! Thank you...my little brain just was simply not thinking in that way!! It seems so simple when I look at this now but I was determined to try to look in-between rather than subtract from a total! I may be back for more help later ;o) Thanks again, Steve "Andy B" wrote: Hi Try: =COUNTIF(A1:A5,""&0)-COUNTIF(A1:A5,""&10) You want a count of all and then take off those that are bigger than 10, I guess. -- Andy. "SP" wrote in message ... I'm having trouble implementing the various formulas I'm seeing posted. I think I want the same thing many others are asking for but must be doing something wrong: I used several including: =COUNTIF(A1:A5,""&0)-COUNTIF(A1:A5,"<"&10) RANGE CONTENTS: 2 | 5 | 17 | 6 | 29 So, I would expect my formula result to equal 3. How do I acheive this? Thanks in advance! SJP~ "John F" wrote: Thanks ALL: I'll try each method so it will hopefully drill in my head somewhere. I'll have to come back after supper to try them out. The prompt response is greatly appreciated. John F. "Aladin Akyurek" wrote: A set of 2 critera that can be taken together as an interval... =COUNTIF(X58:X73,""&Y2)-COUNTIF(X58:X73,"="&Z2) where Y2 <= Z2 like with 23 and 31, repectively, we get a count of numbers that fall in the interval specified by " 23 and < 31". John F wrote: Excel 2003 Looking for a function that will handle: Count AND a range of criterea Something like: =COUNTIF(X58:X73,"23 & <31") =COUNTIF(X58:X73,"30,<58") =COUNTIF(X58:X73,"56,<73") (but this doesn't work) any ideas? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
| help with "criterea" in the sumif function | Excel Worksheet Functions | |||
| Formula to list unique values | Excel Worksheet Functions | |||
| variable range countif | Excel Worksheet Functions | |||
Linear Mode
