Sep 08 2022 02:46 AM
Hey Folks
Not sure if this is possible. I need a formula that will add cells together depending on what is put in another cell.
So if in A it says 1 - I need to add up all the amounts in C that are on the same line as 1.
So if A1, A3, & A4 all show 1. I need the values of C1,C3 & C4 adding together to get me a total value.
Hope this makes sense.
Sep 08 2022 03:24 AM
Sep 08 2022 03:32 AM
Sep 08 2022 04:37 AM
Sep 08 2022 05:04 AM
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)
Sep 08 2022 05:42 AM
Sep 08 2022 06:41 AM
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)
Sep 08 2022 07:06 AM
Sep 08 2022 07:26 AM
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.
Sep 08 2022 07:37 AM
@Hans Vogelaar This is very basic idea. As you notice in tab "Costs" its saying 0, but there is data in the data tab.
Sep 08 2022 08:22 AM
The numbers you want to sum are in column D, so the first argument (the sum_range) should be Data!D:D.
The codes are in column A, so the first criteria range should be Data!A:A.
=SUMIFS(Data!D:D,Data!A:A,"A-QW",Data!B:B,">="&$S$1,Data!B:B,"<="&$T$1)
sum_rng, crit_rng1, criteria_1, crit_rng2, criteria2, crit_rng3, criteria3