Forum Discussion
Formula for adding certain cells
Let's say you have dates in column D. This time, we'll use SUMIFS:
=SUMIFS(C1:C100, A1:A100, 1, D1:D100, ">="&DATE(2022, 8, 1), D1:D100, "<="&DATE(2022, 8, 31))
Instead of including the literal dates in the formula, you can refer to cells - say K1 contains 01/08/2022 and K2 contains 31/08/2022:
=SUMIFS(C1:C100, A1:A100, 1, D1:D100, ">="&K1, D1:D100, "<="&K2)
=SUMIFS(Data!C1:C100,Data!AM1:AM100,"Asda - Cable Maintenance",Data!$E:$E,">="&$S$1,Data!$E:$E,"<="&$T$1)
All ranges must be the same size, so
=SUMIFS(Data!$C$1:$C$100,Data!$AM$1:$AM$100,"Asda - Cable Maintenance",Data!$E$1:$E$100,">="&$S$1,Data!$E$1:$E$100,"<="&$T$1)
- this is only returning 0. I know I'm missing something. I noticed in your original one the "1" was in the middle, so I tried this one =SUMIFS(Data!C1:C100, "Asda - Quoted Works",Data!AM1:AM100,Data!$E$1:$E$100,">="&$S$1,Data!$E$1:$E$100,"<="&$T$1) but i keep getting the old "there is a problem with your formula error. Any ideas.
No, that won't work. The syntax of SUMIFS is different from that of SUMIF:
=SUMIF(criteria_range, criteria, sum_range)
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.