Search code examples
google-sheetssplitcountvlookupgoogle-query-language

If column A is equal to current month then do. I am needing my formula updated please


I have a formula to count number of times the word "test" is on a certain sheet

=COUNTIF(FILTER('Archived D'!E:E,ISNUMBER(MATCH('Archived D'!H:H,Match!$A$2:$A$12,0))),A2) + COUNTIF('IAD'!E:E, A:A)

I need the formula to only count total of "test" when column "A" in "IAD" is equal to the current month. I tried

+ COUNTIFS('IAD'!E:E, A:A, 'IAD'!A:A, MONTH(TODAY()))

That is giving me zero for my count and that isn't right. What am I doing wrong?


Solution

  • try:

    =ARRAYFORMULA(IF(A2:A="",,IFNA(VLOOKUP(A2:A, 
     QUERY({Archive!A3:H; IAD!A:H}, 
     "select Col3,count(Col3) 
      where month(Col1)+1 = "&MONTH(TODAY())&" 
        and Col8 matches '"&TEXTJOIN("|", 1, Names!A2:A)&"' 
      group by Col3"), 2, 0), 0)))
    

    enter image description here