Search code examples
google-sheetssumifs

How to use SUMIF with the criteria as text within a string

I have column A that has dates in the "8-Oct-2021" format, I have column B that has numbers I want summed.

=SUMIF(A2:A, "*Oct*", B2:B)

I currently have the formula above, with the criteria as "*Oct*" because there's text before and after the 'Oct'. I have other months so I want each month added up.

=SUMIFS(B2:B, A2:A, ">="&DATE(2021,10,1), A2:A, "<="&DATE(2021,10,30))

Above does the trick, but I'd like to keep it simple with the first formula. Suggestions?

Solution

  • At the moment you have numbers in range A2:A, not text, because the dates in GS are numbers, so your formula SUMIF() will return 0.

    You can shorten your SUMIFS() formula to =ARRAYFORMULA(SUMIF(month(A2:A), 10, B2:B)) or to =ARRAYFORMULA(SUMIF(text(A2:A, "mmm"), "Oct", B2:B))