Count based on conditions

[acsmiamiguy]

A B
4805 50.00
4805 45.00
4805 0.00
4900 0.00
4900 0.00
5010 48.00
5010 62.00
5010 58.00

In the above table column A has claim numbers, and column B amounts paid. A claim can be either partially paid (#4805); not paid at all (#4900) or fully paid (#5010).

Is it possible to get a count of the number of claims that are partially paid, fully paid or not paid?

Thanks!

 

[Eric W]

Partially paid:

=COUNTIF(A:A,4805)

The other cases work the same.

 

[acsmiamiguy]

More complicated than that. I need to know how many claims are fully, partially or not paid.

 

[Scott T]

How do you know what status the claim is? Do you have a list with total amount due?

 

[Scott T]

Try

[RANGE=cls:xl2bb-100][XR][XH=cs:9]Book1[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][/XR][XR][XH]1[/XH][XD=h:r]4805[/XD][XD=h:r]50[/XD][XD=h:r][/XD][XD=h:l]no payment[/XD][XD=h:r|cls:fx][FORMULA==SUMPRODUCT(--(SUMIF($A$1:$A$10,$G$2:$G$5,$B$1:$B$10)=0))]1[/FORMULA][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:l]Total due[/XD][/XR][XR][XH]2[/XH][XD=h:r]4805[/XD][XD=h:r]45[/XD][XD=h:r][/XD][XD=h:l]Partial[/XD][XD=h:r|cls:fx][FORMULA==SUMPRODUCT(--(SUMIF($A$1:$A$10,$G$2:$G$5,$B$1:$B$10)&lt;$H$2:$H$5)*--(SUMIF($A$1:$A$10,$G$2:$G$5,$B$1:$B$10)&lt;&gt;0))]1[/FORMULA][/XD][XD=h:r][/XD][XD=h:r]4805[/XD][XD=h:r]200[/XD][/XR][XR][XH]3[/XH][XD=h:r]4805[/XD][XD=h:r]0[/XD][XD=h:r][/XD][XD=h:l]fully paid[/XD][XD=h:r|cls:fx][FORMULA==SUMPRODUCT(--(SUMIF($A$1:$A$10,$G$2:$G$5,$B$1:$B$10)=$H$2:$H$5))]2[/FORMULA][/XD][XD=h:r][/XD][XD=h:r]4900[/XD][XD=h:r]666[/XD][/XR][XR][XH]4[/XH][XD=h:r]4900[/XD][XD=h:r]0[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r]5010[/XD][XD=h:r]168[/XD][/XR][XR][XH]5[/XH][XD=h:r]4900[/XD][XD=h:r]0[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r]5777[/XD][XD=h:r]69[/XD][/XR][XR][XH]6[/XH][XD=h:r]5010[/XD][XD=h:r]48[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]7[/XH][XD=h:r]5010[/XD][XD=h:r]62[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]8[/XH][XD=h:r]5777[/XD][XD=h:r]67[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]9[/XH][XD=h:r]5010[/XD][XD=h:r]58[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH]10[/XH][XD=h:r]5777[/XD][XD=h:r]2[/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][XD=h:r][/XD][/XR][XR][XH=cs:9][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2|h:l|fw:b]Cell Formulas[/XH][/XR][XR][XH]Cell[/XH][XH]Formula[/XH][/XR][XR][XD]E1[/XD][XD]=SUMPRODUCT(--(SUMIF($A$1:$A$10,$G$2:$G$5,$B$1:$B$10)=0))[/XD][/XR][XR][XD]E2[/XD][XD]=SUMPRODUCT(--(SUMIF($A$1:$A$10,$G$2:$G$5,$B$1:$B$10)<$H$2:$H$5)*--(SUMIF($A$1:$A$10,$G$2:$G$5,$B$1:$B$10)<>0))[/XD][/XR][XR][XD]E3[/XD][XD]=SUMPRODUCT(--(SUMIF($A$1:$A$10,$G$2:$G$5,$B$1:$B$10)=$H$2:$H$5))[/XD][/XR][/RANGE]

 

[acsmiamiguy]

How do you know what status the claim is? Do you have a list with total amount due?

If there is any amount greater than zero, then there is a payment...

 

[Eric W]

Sorry, I misunderstood your example. To echo Scott's question, how do you know if the account is paid or not? Assuming you have a list somewhere, you could do something like this:

	A	B	C	D	E	F	G	H	I
1	Account	Payment		Account	Owed		Accounts not paid	1
2	4805	$ 50.00		4805	100		Accounts partially paid	1
3	4805	$ 45.00		4900	125		Accounts fully paid	1
4	4805	$ -		5010	168
5	4900	$ -
6	4900	$ -
7	5010	$ 48.00
8	5010	$ 62.00
9	5010	$ 58.00

<tbody>
</tbody>

Sheet12​

Worksheet Formulas Cell Formula H1 =SUMPRODUCT(--(SUMIF(A:A,$D$2:$D$10,B:B)=0)) H2 =COUNTA($D$2:$D$10)-H1-H3 H3 =SUMPRODUCT(--(SUMIF(A:A,$D$2:$D$10,B:B)=$E$2:E$10),--($D$2:$D$10<>"")) <tbody> </tbody>

<tbody>
</tbody>