Counting Payments Based on Rows and Columns

[rpdonovan]

I am trying to count the number of payments (non-zero) made in a fiscal year based on the property. I can do a Countif for each column but there can be a large number of columns (properties) so I would like to be able to create a formula using the whole array that can reference the property and the fiscal year.

year	Period	North	West	South
2019	1	100	50	75
2019	6	100	50	75
2019	9	0	50	75
2020	1	100	50	75
2020	6	100	0	75
2020	9	100	0	75
2021	1	150	0	75
2021	6	150	0	75
2021	9	0	0	75

<tbody>
</tbody>

This is the table I would like to create.

	2019	2020	2021
North	2	3	2
West	3	1	0
South	3	3	3

<tbody>
</tbody>

 

[Marcelo Branco]

Try

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	year​	Period​	North​	West​	South​			2019​	2020​	2021​
2​	2019​	1​	100​	50​	75​		North​	2​	3​	2​
3​	2019​	6​	100​	50​	75​		West​	3​	1​	0​
4​	2019​	9​	0​	50​	75​		South​	3​	3​	3​
5​	2020​	1​	100​	50​	75​
6​	2020​	6​	100​	0​	75​
7​	2020​	9​	100​	0​	75​
8​	2021​	1​	150​	0​	75​
9​	2021​	6​	150​	0​	75​
10​	2021​	9​	0​	0​	75​

Formula in H2 copied across and down
=COUNTIFS($A$2:$A$10,H$1,INDEX($C$2:$E$10,0,MATCH($G2,$C$1:$E$1,0)),">0")

Hope this helps

M.

 

[Nine Zero]

do a countifs for that

assuming the first table begins on A1 and the second table begins on G1

this is for North 2019
=COUNTIFS(A:A,H1,C:C,">0")

 

[rpdonovan]

This is exactly what I needed. Thank you very much.