I’m working on a Google Sheets project where I need to calculate the total amount from a list of transactions. Each transaction has specific criteria that I want to filter by, including the department, account number, and the month of the transaction, which is represented by numbers from 1 to 12. I have a reference in cell K1 that indicates the current month, and G3 holds the department criteria.
However, when I add the month condition, it generates incorrect results. If I exclude the month filter, it totals up correctly, but I want to include it. How can I adjust my formula to combine these criteria correctly?
Your SUMIFS syntax looks fine - the issue is with your criteria logic. You’re using “<=”&$K$1 for the month comparison, which gives you cumulative totals for all months up to the current one. That’s probably why removing that condition fixes your total. Want just the current month? Drop the “<=” and use $K$1 by itself. But here’s the catch - if column F has actual dates instead of month numbers, you’ll need to wrap it with MONTH() in your criteria. I’ve also seen this break when K1 and column F have different data types. Make sure they match - sometimes one’s text and the other’s numeric, so the comparison fails without any error message.
you’re getting cumulative totals instead of just that month’s data. remove the “<=” operator from your third criteria - change it to just ‘Data’!F:F, $K$1 instead of ‘Data’!F:F, “<=”&$K$1. also make sure column F actually has month numbers (1-12), not dates or text - otherwise the comparison won’t work.
I’ve hit this same SUMIFS issue with month filtering. Your third condition “$K$1” is summing all months up to the current month, not just the current month. Change it to just “$K$1” without the “<=”. Check that your month values in column F are consistent numbers, not text. I’ve seen months stored as “01” vs “1” which breaks comparisons. Also verify A27 has the right account number format matching column C - leading zeros or spaces can kill the criteria match.