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.
[TABLE="width: 506"]
<colgroup><col span="5"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Count[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Total Items to Count[/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fox[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Rabbit[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD] 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sqirrel[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD] 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Squirrel[/TD]
[TD] 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Squirrel[/TD]
[TD] 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Squirrel[/TD]
[TD] 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Squirrel[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Squirrel[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Squirrel[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="width: 506"]
<colgroup><col span="5"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Count[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Total Items to Count[/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fox[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Rabbit[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD] 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sqirrel[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD] 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Squirrel[/TD]
[TD] 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rabbit[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Squirrel[/TD]
[TD] 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Squirrel[/TD]
[TD] 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Squirrel[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Squirrel[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Squirrel[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.