Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
incremental counting based on criteria
I am out of ideas. I need to count how many times a category is repeated.
Here's what I have: name category 1 a aa 2 b aa 3 c aa 4 d bb 5 e aa 6 f aa 7 g bb 8 h cc 9 i cc 10 j aa Here's what I need: name category count 1 a aa 1 2 b aa 2 3 c aa 3 4 d bb 1 5 e aa 4 6 f aa 5 7 g bb 2 8 h cc 1 9 i cc 2 10 j aa 6 Same thing sorted (if it helps): name category count 1 a aa 1 2 b aa 2 3 c aa 3 4 e aa 4 5 f aa 5 6 j aa 6 7 d bb 1 8 g bb 2 9 h cc 1 10 i cc 2 Thank you in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
incremental counting based on criteria
Try...
C1, copied down: =COUNTIF($B$1:B1,B1) Hope this helps! http://www.xl-central.com In article , style0 wrote: I am out of ideas. I need to count how many times a category is repeated. Here's what I have: name category 1 a aa 2 b aa 3 c aa 4 d bb 5 e aa 6 f aa 7 g bb 8 h cc 9 i cc 10 j aa Here's what I need: name category count 1 a aa 1 2 b aa 2 3 c aa 3 4 d bb 1 5 e aa 4 6 f aa 5 7 g bb 2 8 h cc 1 9 i cc 2 10 j aa 6 Same thing sorted (if it helps): name category count 1 a aa 1 2 b aa 2 3 c aa 3 4 e aa 4 5 f aa 5 6 j aa 6 7 d bb 1 8 g bb 2 9 h cc 1 10 i cc 2 Thank you in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
incremental counting based on criteria
Worked perfectly, thank you so much!
Now maybe you can help me understand what "$B$1:B1,B1" means. "Domenic" wrote: Try... C1, copied down: =COUNTIF($B$1:B1,B1) Hope this helps! http://www.xl-central.com |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
incremental counting based on criteria
You're very welcome!
Basically, when the formula is first entered in C1, the range is $B$1:B1 and the criteria is B1. When the formula is copied and pasted or dragged down to C2, the range changes to $B$1:B2 and the criteria changes to B2. As you can see, the starting reference for the range does not change and remains at $B$1, whereas the ending reference for the range changes from B1 to B2, and the criteria changes from B1 to B2. That's because the starting reference is an absolute reference (specified by the $ signs) and so does not change when copied, whereas the reference for the ending reference and criteria are relative references (relative to the cell housing the formula). For more information, have a look at Excel's help files under 'absolute and relative references'. Hope this helps! http://www.xl-central.com In article , style0 wrote: Worked perfectly, thank you so much! Now maybe you can help me understand what "$B$1:B1,B1" means. "Domenic" wrote: Try... C1, copied down: =COUNTIF($B$1:B1,B1) Hope this helps! http://www.xl-central.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
incremental counting based on criteria
Got it, thanks again!
"Domenic" wrote: You're very welcome! Basically, when the formula is first entered in C1, the range is $B$1:B1 and the criteria is B1. When the formula is copied and pasted or dragged down to C2, the range changes to $B$1:B2 and the criteria changes to B2. As you can see, the starting reference for the range does not change and remains at $B$1, whereas the ending reference for the range changes from B1 to B2, and the criteria changes from B1 to B2. That's because the starting reference is an absolute reference (specified by the $ signs) and so does not change when copied, whereas the reference for the ending reference and criteria are relative references (relative to the cell housing the formula). For more information, have a look at Excel's help files under 'absolute and relative references'. Hope this helps! http://www.xl-central.com In article , style0 wrote: Worked perfectly, thank you so much! Now maybe you can help me understand what "$B$1:B1,B1" means. "Domenic" wrote: Try... C1, copied down: =COUNTIF($B$1:B1,B1) Hope this helps! http://www.xl-central.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting based on criteria from two cells??? | Excel Worksheet Functions | |||
Counting based on multiple criteria | Excel Discussion (Misc queries) | |||
Function - counting based on criteria | Excel Worksheet Functions | |||
counting occurences based on two criteria | Excel Discussion (Misc queries) | |||
counting based on criteria | Excel Worksheet Functions |