PShingadia
New Member
- Joined
- Aug 5, 2015
- Messages
- 47
Hi
Just wondered if anyone can help with a formula to count out items in a list based on a count in another range. So in the example below the little box says how many to count in total and from the list I wish to count out that many and it more to show as zero.
<colgroup><col span="5"><col><col></colgroup><tbody>
</tbody>
So box says 5 foxes and result I want is formula that marks out 5 foxes 1 to 5 and any more show as zero. Marked out 2 rabbits and rest shown as zero, etc. I have the formula = IF(AND(COUNTIF($C$3:$C3,$H$3)<=$I$3, $C3=$H$3), COUNTIF($C$3:$C3,$H$3), 0) but don't know how to apply over the range.
Thanks for your help.
Just wondered if anyone can help with a formula to count out items in a list based on a count in another range. So in the example below the little box says how many to count in total and from the list I wish to count out that many and it more to show as zero.
Count | Total Items to Count | |||||
Fox | 1 | Fox | 5 | |||
Fox | 2 | Rabbit | 2 | |||
Rabbit | 1 | Sqirrel | 3 | |||
Rabbit | 2 | |||||
Rabbit | - | |||||
Rabbit | - | |||||
Fox | 3 | |||||
Fox | 4 | |||||
Rabbit | - | |||||
Squirrel | 1 | |||||
Rabbit | - | |||||
Squirrel | 2 | |||||
Fox | 5 | |||||
Squirrel | 3 | |||||
Squirrel | - | |||||
Squirrel | - | |||||
Squirrel | - | |||||
Fox | - | |||||
Fox | - | |||||
Fox | - |
<colgroup><col span="5"><col><col></colgroup><tbody>
</tbody>
So box says 5 foxes and result I want is formula that marks out 5 foxes 1 to 5 and any more show as zero. Marked out 2 rabbits and rest shown as zero, etc. I have the formula = IF(AND(COUNTIF($C$3:$C3,$H$3)<=$I$3, $C3=$H$3), COUNTIF($C$3:$C3,$H$3), 0) but don't know how to apply over the range.
Thanks for your help.