Count Only Fields That Are Greater Than 0

[jski21]

Good afternoon Mr. Excel Team,

I have the following data:

B&H Board-Up Disbursements PY48 2022-2023.xlsx
	G	H	I	J	K	L
1			Request No.	Period	Amount	# of Services
2			1	Jun-22	29,420.00	57
3			2	Jul-22	-	-
4			3	Aug-22	-	-
5			4	Sep-22	50,564.50	127
6			5	Oct-22	70,695.50	204
7			6	Nov-22	134,494.00	1,193
8			7	Dec-22
9	Request No.	6	8	Jan-23
10			9	Feb-23
11	Date	8/31/2023	10	Mar-23
12			11	Apr-23
13	Reporting Period	Nov-22	12	May-23
14			13	FINAL
Cover Sheet

Cell Formulas
Range	Formula
J2:J14	J2	='Equipment & Contractual'!I2
K2:L14	K2	=IF('Equipment & Contractual'!J2<>"",'Equipment & Contractual'!J2,"")
H9	H9	=IF(INDEX(K2:K13,COUNT(K2:K13))<>"",OFFSET(INDEX(K2:K13,COUNT(K2:K13)),0,-2))

Data is essentially payments submitted. Cell H9 calculates the payment number using the formula:

=IF(INDEX(K2:K13,COUNT(K2:K13))<>"",OFFSET(INDEX(K2:K13,COUNT(K2:K13)),0,-2))


Sometimes there is no payment. In that case, I don't want to count it as nothing will be submitted. This is really payment #4 not #6. How can this formula be adjusted to do that or perhaps there is a better way?

Thanks as always for the consideration and guidance.

jski

 

[DanteAmor]

Deleted.

 

[jski21]

Good afternoon Mr. Excel Team,

I have the following data:

B&H Board-Up Disbursements PY48 2022-2023.xlsx
	G	H	I	J	K	L
1			Request No.	Period	Amount	# of Services
2			1	Jun-22	29,420.00	57
3			2	Jul-22	-	-
4			3	Aug-22	-	-
5			4	Sep-22	50,564.50	127
6			5	Oct-22	70,695.50	204
7			6	Nov-22	134,494.00	1,193
8			7	Dec-22
9	Request No.	6	8	Jan-23
10			9	Feb-23
11	Date	8/31/2023	10	Mar-23
12			11	Apr-23
13	Reporting Period	Nov-22	12	May-23
14			13	FINAL
Cover Sheet

Cell Formulas
Range	Formula
J2:J14	J2	='Equipment & Contractual'!I2
K2:L14	K2	=IF('Equipment & Contractual'!J2<>"",'Equipment & Contractual'!J2,"")
H9	H9	=IF(INDEX(K2:K13,COUNT(K2:K13))<>"",OFFSET(INDEX(K2:K13,COUNT(K2:K13)),0,-2))

Data is essentially payments submitted. Cell H9 calculates the payment number using the formula:

=IF(INDEX(K2:K13,COUNT(K2:K13))<>"",OFFSET(INDEX(K2:K13,COUNT(K2:K13)),0,-2))


Sometimes there is no payment. In that case, I don't want to count it as nothing will be submitted. This is really payment #4 not #6. How can this formula be adjusted to do that or perhaps there is a better way?

Thanks as always for the consideration and guidance.

jski

A brief walk to the men's room brought forth an epiphany. Will use this: =COUNTIF(K2:K14,">0").

And then to add the numbers in column K based on the result in H9: =SUMIF(J2:J14,"<"&EOMONTH(H13,-1)+1,K2:K14)