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
Account	Payment	Account	Owed	Accounts not paid
$ 50.00	Accounts partially paid
Accounts fully paid

<tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

[TD="align: center"]3[/TD]
[TD="align: right"]4805[/TD]
[TD="align: right"] $ 45.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4900[/TD]
[TD="align: right"]125[/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]4[/TD]
[TD="align: right"]4805[/TD]
[TD="align: right"] $ -[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5010[/TD]
[TD="align: right"]168[/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"]4900[/TD]
[TD="align: right"] $ -[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/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"]4900[/TD]
[TD="align: right"] $ -[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/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"]5010[/TD]
[TD="align: right"] $ 48.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5010[/TD]
[TD="align: right"] $ 62.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]5010[/TD]
[TD="align: right"] $ 58.00[/TD]

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

</tbody>

Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH]Formula[/TH]
[/TR]
[TR]
[TH]H1[/TH]
[TD="align: left"]=SUMPRODUCT(--(SUMIF(A:A,$D$2:$D$10,B:B)=0))[/TD]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=COUNTA($D$2:$D$10)-H1-H3[/TD]
[/TR]
[TR]
[TH]H3[/TH]
[TD="align: left"]=SUMPRODUCT(--(SUMIF(A:A,$D$2:$D$10,B:B)=$E$2:E$10),--($D$2:$D$10<>""))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]