count function
How do I count the number of times a number is between two numbers?
range of cells is B5:B33 would like to know how many times a number shows up that is 124 but <150 |
Try either:
=SUMPRODUCT((B5:B33124)*(B5:B33<150)) or: =COUNTIF(B5:B33,""&124)-COUNTIF(B5:B33,"="&150) -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "ChrisC" wrote in message ... How do I count the number of times a number is between two numbers? range of cells is B5:B33 would like to know how many times a number shows up that is 124 but <150 |
=COUNTIF(B5:B33,""&124)-COUNTIF(B5:B33,"="&150)
No need for the '&' bits unless you putting in cell references in place of those numbers =COUNTIF(B5:B33,"124")-COUNTIF(B5:B33,"=150") -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Max" wrote in message ... Try either: =SUMPRODUCT((B5:B33124)*(B5:B33<150)) or: =COUNTIF(B5:B33,""&124)-COUNTIF(B5:B33,"="&150) -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "ChrisC" wrote in message ... How do I count the number of times a number is between two numbers? range of cells is B5:B33 would like to know how many times a number shows up that is 124 but <150 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004 |
Thanks for the refinement, Ken !
Guess softcoding habits die-hard <g -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- |
LOL :-)
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Max" wrote in message ... Thanks for the refinement, Ken ! Guess softcoding habits die-hard <g -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <atyahoo<dotcom --- --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004 |
Thanks, this work great!
"Ken Wright" wrote: =COUNTIF(B5:B33,""&124)-COUNTIF(B5:B33,"="&150) No need for the '&' bits unless you putting in cell references in place of those numbers =COUNTIF(B5:B33,"124")-COUNTIF(B5:B33,"=150") -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Max" wrote in message ... Try either: =SUMPRODUCT((B5:B33124)*(B5:B33<150)) or: =COUNTIF(B5:B33,""&124)-COUNTIF(B5:B33,"="&150) -- Rgds Max xl 97 --- Please respond in thread xdemechanik <atyahoo<dotcom ---- "ChrisC" wrote in message ... How do I count the number of times a number is between two numbers? range of cells is B5:B33 would like to know how many times a number shows up that is 124 but <150 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.788 / Virus Database: 533 - Release Date: 01/11/2004 |
All times are GMT +1. The time now is 12:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com