Counting data between 2 values
Hi,
I have a list of durations on 1 cell (as below). 1 21 54 72 95 170 etc.... I want to count how many of them are between 0 and 60, how many between 61 and 120, so on and so forth. I have tried using Countif, but can't get it to work. My last attempt was =count(if(and('Site list_South'!$E$2:$E$5000=0,'Site list_South'!$E$2:$E$5000<=60)) and it failed miserably. Please Help!!!! |
Counting data between 2 values
hi CMO,
you could use sumproduct as: =sumproduct(--(a1:a100=1)*--(a1:a100<=60)) hope this helps Regards from Brazil Marcelo "CMO" escreveu: Hi, I have a list of durations on 1 cell (as below). 1 21 54 72 95 170 etc.... I want to count how many of them are between 0 and 60, how many between 61 and 120, so on and so forth. I have tried using Countif, but can't get it to work. My last attempt was =count(if(and('Site list_South'!$E$2:$E$5000=0,'Site list_South'!$E$2:$E$5000<=60)) and it failed miserably. Please Help!!!! |
Counting data between 2 values
=countif(e2:e5000,"=0")-countif(e2:e5000,"60") =countif(e2:e5000,"=61")-countif(e2:e5000,"120") =countif(e2:e5000,"=121")-countif(e2:e5000,"180") =countif(e2:e5000,"=181")-countif(e2:e5000,"240") -- Bearacade ------------------------------------------------------------------------ Bearacade's Profile: http://www.excelforum.com/member.php...o&userid=35016 View this thread: http://www.excelforum.com/showthread...hreadid=549508 |
Counting data between 2 values
Thanks a lot, works perfectly.
Goodbye from sunny England:-) "Marcelo" wrote: hi CMO, you could use sumproduct as: =sumproduct(--(a1:a100=1)*--(a1:a100<=60)) hope this helps Regards from Brazil Marcelo "CMO" escreveu: Hi, I have a list of durations on 1 cell (as below). 1 21 54 72 95 170 etc.... I want to count how many of them are between 0 and 60, how many between 61 and 120, so on and so forth. I have tried using Countif, but can't get it to work. My last attempt was =count(if(and('Site list_South'!$E$2:$E$5000=0,'Site list_South'!$E$2:$E$5000<=60)) and it failed miserably. Please Help!!!! |
Counting data between 2 values
* and -- is built-in redundancy, only need one or the other
=sumproduct(--(a1:a100=1),--(a1:a100<=60)) or =sumproduct((a1:a100=1)*(a1:a100<=60)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Marcelo" wrote in message ... hi CMO, you could use sumproduct as: =sumproduct(--(a1:a100=1)*--(a1:a100<=60)) hope this helps Regards from Brazil Marcelo "CMO" escreveu: Hi, I have a list of durations on 1 cell (as below). 1 21 54 72 95 170 etc.... I want to count how many of them are between 0 and 60, how many between 61 and 120, so on and so forth. I have tried using Countif, but can't get it to work. My last attempt was =count(if(and('Site list_South'!$E$2:$E$5000=0,'Site list_South'!$E$2:$E$5000<=60)) and it failed miserably. Please Help!!!! |
Counting data between 2 values
Hi Bob, thanks for the feedback,
regards Marcelo "Bob Phillips" escreveu: * and -- is built-in redundancy, only need one or the other =sumproduct(--(a1:a100=1),--(a1:a100<=60)) or =sumproduct((a1:a100=1)*(a1:a100<=60)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Marcelo" wrote in message ... hi CMO, you could use sumproduct as: =sumproduct(--(a1:a100=1)*--(a1:a100<=60)) hope this helps Regards from Brazil Marcelo "CMO" escreveu: Hi, I have a list of durations on 1 cell (as below). 1 21 54 72 95 170 etc.... I want to count how many of them are between 0 and 60, how many between 61 and 120, so on and so forth. I have tried using Countif, but can't get it to work. My last attempt was =count(if(and('Site list_South'!$E$2:$E$5000=0,'Site list_South'!$E$2:$E$5000<=60)) and it failed miserably. Please Help!!!! |
All times are GMT +1. The time now is 06:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com