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.



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]year[/TD]
[TD]Period[/TD]
[TD]North[/TD]
[TD]West[/TD]
[TD]South[/TD]
[/TR]
[TR]
[TD]2019[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]2019[/TD]
[TD]6[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]2019[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]2020[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]50[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]2020[/TD]
[TD]6[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]2020[/TD]
[TD]9[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]2021[/TD]
[TD]1[/TD]
[TD]150[/TD]
[TD]0[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]2021[/TD]
[TD]6[/TD]
[TD]150[/TD]
[TD]0[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]2021[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]75[/TD]
[/TR]
</tbody>[/TABLE]

This is the table I would like to create.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[TD]2021[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

year​

[/td][td]

Period​

[/td][td]

North​

[/td][td]

West​

[/td][td]

South​

[/td][td][/td][td][/td][td]

2019​

[/td][td]

2020​

[/td][td]

2021​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

2019​

[/td][td]

1​

[/td][td]

100​

[/td][td]

50​

[/td][td]

75​

[/td][td][/td][td]

North​

[/td][td]

2​

[/td][td]

3​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

2019​

[/td][td]

6​

[/td][td]

100​

[/td][td]

50​

[/td][td]

75​

[/td][td][/td][td]

West​

[/td][td]

3​

[/td][td]

1​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

2019​

[/td][td]

9​

[/td][td]

0​

[/td][td]

50​

[/td][td]

75​

[/td][td][/td][td]

South​

[/td][td]

3​

[/td][td]

3​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

2020​

[/td][td]

1​

[/td][td]

100​

[/td][td]

50​

[/td][td]

75​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

2020​

[/td][td]

6​

[/td][td]

100​

[/td][td]

0​

[/td][td]

75​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

2020​

[/td][td]

9​

[/td][td]

100​

[/td][td]

0​

[/td][td]

75​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

2021​

[/td][td]

1​

[/td][td]

150​

[/td][td]

0​

[/td][td]

75​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

2021​

[/td][td]

6​

[/td][td]

150​

[/td][td]

0​

[/td][td]

75​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

2021​

[/td][td]

9​

[/td][td]

0​

[/td][td]

0​

[/td][td]

75​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.