Counting with Two Ranges and Criteria

[PShingadia]

Hi

Just wondered if anyone can help with a formula to count out items in a list based on a count in another range with a criteria. 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 with an added criteria and if more to show as zero. This is similar to a previous post which I have used but can't seem to get it to work with the additional criteria. So in the example below the little box says how many to count out with weight of 10 and require a formula to go down and count these out in sequence and once it reaches the number required to put zero to any others.

The formula I am using in the Result Getting column is: =IF(COUNTIFS($A$3:A3,A3,$B$3:B3,$I$8)> VLOOKUP(A3,$F$8:$G$10,2,0), 0, COUNTIFS($A$3:A3,A3,$B$3:B3,$I$8)) but this largely works but falls down at the items shown in red. The Results Required column shows what I should be getting.

Any help with tweaky this would be really appreciated.

[TABLE="width: 1076"]
<colgroup><col span="3"><col><col><col><col><col span="8"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Type[/TD]
[TD]Weight[/TD]
[TD]Result Getting[/TD]
[TD]Result Required[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD]10[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD][/TD]
[TD="colspan: 10"][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD]10[/TD]
[TD] 2[/TD]
[TD] 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD]10[/TD]
[TD] 3[/TD]
[TD] 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]20[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]20[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD][/TD]
[TD]Type[/TD]
[TD]Count[/TD]
[TD][/TD]
[TD]Weight[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]10[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD][/TD]
[TD]Fox[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]10[/TD]
[TD] 1[/TD]
[TD] 1[/TD]
[TD][/TD]
[TD]Cat[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]10[/TD]
[TD] 2[/TD]
[TD] 2[/TD]
[TD][/TD]
[TD]Dog[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD]30[/TD]
[TD] 3[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD]20[/TD]
[TD] 3[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD]10[/TD]
[TD] 4[/TD]
[TD] 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD]10[/TD]
[TD] 5[/TD]
[TD] 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD]30[/TD]
[TD] 5[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fox[/TD]
[TD]10[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]20[/TD]
[TD] 1[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]10[/TD]
[TD] 2[/TD]
[TD] 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]20[/TD]
[TD] 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]10[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]30[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]30[/TD]
[TD] 2[/TD]
[TD] - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]10[/TD]
[TD] 3[/TD]
[TD] 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

You can add another IF text at the beginning of the formula:

Book1
	A	B	C	D	E	F	G	H	I
2	Type	Weight	Result Getting	Result Required
3	Fox	10	1	1
4	Fox	10	2	2
5	Fox	10	3	3
6	Cat	20	0	0
7	Cat	20	0	0		Type	Count		Weight
8	Cat	10	1	1		Fox	5		10
9	Dog	10	1	1		Cat	4
10	Dog	10	2	2		Dog	2
11	Fox	30	3	0
12	Fox	20	3	0
13	Fox	10	4	4
14	Fox	10	5	5
15	Fox	30	5	0
16	Fox	10	0	0
17	Cat	20	1	0
18	Cat	10	2	2
19	Dog	20	2	0
20	Dog	10	0	0
21	Dog	30	0	0
22	Cat	30	2	0
23	Cat	10	3	3
Sheet39

Cell Formulas
Range	Formula
D3	=IF(B3=I$8,IF(COUNTIFS($A$3:A3,A3,$B$3:B3,$I$8)>VLOOKUP(A3,$F$8:$G$10,2,0),0,COUNTIFS($A$3:A3,A3,$B$3:B3,$I$8)),0)

D3 formula copied down.

 

[PShingadia]

Hi,

You can add another IF text at the beginning of the formula:

	A	B	C	D	E	F	G	H	I
Type	Weight	Result Getting	Result Required
Fox
Fox
Fox
Cat
Cat	Type	Count	Weight
Cat	Fox
Dog	Cat
Dog	Dog
Fox
Fox
Fox
Fox
Fox
Fox
Cat
Cat
Dog
Dog
Dog
Cat
Cat

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet39​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=IF(B3=I$8,IF(COUNTIFS($A$3:A3,A3,$B$3:B3,$I$8)>VLOOKUP(A3,$F$8:$G$10,2,0),0,COUNTIFS($A$3:A3,A3,$B$3:B3,$I$8)),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



D3 formula copied down.

Thank you!!! Long day yesterday and cracked most of it and simple when you know how!

 

[jtakw]

You're welcome.