Having trouble with 2 criteria SUMIFS. I want to SUM if Column A=rent AND Column C "contains July"
I've tried SUMIFS(sum-values, test-values, condition, test-values, condition) with second condition "July*" or "July"
Please Help!
AAAA. BBBB. CCCC
Rent. $500 June 3
Marketing $325 June 15
Admin $150 June 23
Rent $500 July 3
If you format column C as text, not date & time, your formula will work.
Or you can leave it as date&time and insert a column D with the formula =MONTH(C) and your formula for June would be
=SUMIFS(B,A,"rent",D,6)
Or you can leave it as date & time and compare it to other dates. In that case, the year (which is not showing) will be part of the formula.
=SUMIFS(B,A,"rent",C,">=DATE(2021,6,1)",C,"<DATE(2021,7,1))
If you format column C as text, not date & time, your formula will work.
Or you can leave it as date&time and insert a column D with the formula =MONTH(C) and your formula for June would be
=SUMIFS(B,A,"rent",D,6)
Or you can leave it as date & time and compare it to other dates. In that case, the year (which is not showing) will be part of the formula.
=SUMIFS(B,A,"rent",C,">=DATE(2021,6,1)",C,"<DATE(2021,7,1))
The first one should have worked. Take a look at your data in column A. If there are extra spaces or punctuation like I see with "Rent." (with a period) vs "Rent" (no period), it won't be a match. Capitalization does not matter but the words have to be exact matches.
I have no idea what happened to the second formula. It got totally mangled. I don't recall if I typed it into the post or copy/pasted it but it is a mess. It should have been
=SUMIFS(B,A,"rent",C,">="&DATE(2021,6,1),C,"<"&DATE(2021,7,1))
@badunit - these didn't work. Any other suggestions?
I'm not sure what's going on, but I think your quotations are throwing it off. Also, I'm not able to put a formula within a formula, the DATE(2021,6,1) part of the equation isn't working.
I feel like it should be easier than this... 2 conditions, 1 sum.
Thanks for the help.
daveywavey34 wrote:
I feel like it should be easier than this... 2 conditions, 1 sum.
Perhaps you have invisible characters after the values in column A?
This works here (using a wildcard):
=SUMIFS(Table 1::B,Table 1::A,"Rent*",Table 1::D,7)
Or, if your region uses , as a decimal separator:
=SUMIFS(Table 1::B;Table 1::A;"Rent*";Table 1::D;7)
SG
SUMIFS - How to sum criteria A (if cell = then sum) AND criteria B (if cell contains "the letters XYZ" then sum)
