In my google sheet, I have trade entries with dates. I am trying to prepare sheet which can provide the number of trades in each months depends upon the year selected. I am trying use filter function to filter the year first, and then month and then trade count. But current function always giving zero result. Is this approach correct ?
1 Answers
If you bind to your formula, you don't need to apply the filter function twice, because in this function you can apply multiple criteria to filter the data FILTER(range, condition1, [condition2, ...]).
In your case, you can apply the following formula =COUNTIF(filter(YEAR(A2:A),MONTH(A2:A)=1,B2:B="Trade"),F2), where filtering by month and filtering by "Trade" takes place at once
You can also use one array formula =ArrayFormula(countif(YEAR(A2:A)&MONTH(A2:A)&B2:B,F2&SEQUENCE(1,12,1,1)&"Trade")) to perform this task, which will do the same thing as the 12 countif() formulas.
Here we form the data array YEAR(A2:A) & MONTH(A2:A) & B2:B, which will assemble the data into the following form
which we will check for consistency with Year & Month & "Trade" data, but only instead of one month you can substitute all 12 months using the formula SEQUENCE(1,12,1,1,1)
Related