using COUNTIF I want to count multiple values
Hello I have a spreadsheet that I need to count up to 500 items for. For
example how many people between the ages of 19 and 29 fell. right now I am doing ((COUNTIF(a1:a500,"19")+(COUNTIF(a1:a500,"20")) and so on. I knwo there has to be an easier way! So the example below: a 1 19 2 22 3 24 4 55 5 76 6 44 I would want a b 1 19-29 3 2 30-49 1 3 50-69 1 4 70-79 1 Also if I could get help with this I also will need to count time ranges e.i. 0100 to 0900. I know there is a way to do these, but I can nto figure it out - thanks!! |
using COUNTIF I want to count multiple values
Would suggest you to place the query range in two cells.. Suppose you have
data in Col A starting from row 1. B1 = 19 (start range ) C1 = 29 (end range) Use this formula in D1. Adjust the row count to suit your requirement =COUNTIF(A1:A100,"" & B1-1)-COUNTIF(A1:A100,"" & C1) If this post helps click Yes --------------- Jacob Skaria "ericaamousseau" wrote: Hello I have a spreadsheet that I need to count up to 500 items for. For example how many people between the ages of 19 and 29 fell. right now I am doing ((COUNTIF(a1:a500,"19")+(COUNTIF(a1:a500,"20")) and so on. I knwo there has to be an easier way! So the example below: a 1 19 2 22 3 24 4 55 5 76 6 44 I would want a b 1 19-29 3 2 30-49 1 3 50-69 1 4 70-79 1 Also if I could get help with this I also will need to count time ranges e.i. 0100 to 0900. I know there is a way to do these, but I can nto figure it out - thanks!! |
using COUNTIF I want to count multiple values
Use 2 cells to hold your boundaries:
...........C..........D 1.......19.........29 2.......30.........49 3.......50.........69 4.......70.........79 Formula in E1: =COUNTIF(A$1:A$6,"="&C1)-COUNTIF(A$1:A$6,""&D1) Copy down to E4 -- Biff Microsoft Excel MVP "ericaamousseau" wrote in message ... Hello I have a spreadsheet that I need to count up to 500 items for. For example how many people between the ages of 19 and 29 fell. right now I am doing ((COUNTIF(a1:a500,"19")+(COUNTIF(a1:a500,"20")) and so on. I knwo there has to be an easier way! So the example below: a 1 19 2 22 3 24 4 55 5 76 6 44 I would want a b 1 19-29 3 2 30-49 1 3 50-69 1 4 70-79 1 Also if I could get help with this I also will need to count time ranges e.i. 0100 to 0900. I know there is a way to do these, but I can nto figure it out - thanks!! |
using COUNTIF I want to count multiple values
ericaamousseau wrote:
Hello I have a spreadsheet that I need to count up to 500 items for. For example how many people between the ages of 19 and 29 fell. right now I am doing ((COUNTIF(a1:a500,"19")+(COUNTIF(a1:a500,"20")) and so on. I knwo there has to be an easier way! So the example below: a 1 19 2 22 3 24 4 55 5 76 6 44 I would want a b 1 19-29 3 2 30-49 1 3 50-69 1 4 70-79 1 Also if I could get help with this I also will need to count time ranges e.i. 0100 to 0900. I know there is a way to do these, but I can nto figure it out - thanks!! For 19 <= A <= 29 try =SUMPRODUCT(($A$1:$A$6=19)*($A$1:$A$6<=29)) Vary the ranges and comparisons to suit. |
All times are GMT +1. The time now is 11:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com