I currently have a table that presents different transactions by year and month, as per the example below.
I want to be able to count how many transactions were over 10, but only for the month and year that I selected, as per cell B1 and B2. In that example, I have selected 2019 and March, which should return 1. As there was 1 transaction in March 2019 that exceeded 10.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Year: | 2019 | Month | 2020 | 2019 | 2018 | Over 10? | |
| 2 | Month: | Mar | Jan | 10 | 5 | 15 | 0 | |
| 3 | Jan | 20 | 20 | 10 | 0 | |||
| 4 | Feb | 10 | 25 | 25 | 0 | |||
| 5 | Feb | 5 | 30 | 10 | 0 | |||
| 6 | Mar | 20 | 15 | 5 | 1 | |||
| 7 | Mar | 25 | 5 | 20 | 0 |
To do that, I created a helper column on the side, in column H. My goal is to obtain the values you see in this column.
So far, to obtain the correct month, I have written the following formula:
=IF(AND(ROW(D2)>=MATCH($B$2,D:D,0),$F2>10),1,0)
However, instead of directly referring to column F for 2019, I want to have the formula refer to cell B1. So that if I change the value in B1 to 2020, then it would apply the formula to column E.
Does anyone know how I can amend that part of the formula to achieve this?
