Formula for adding certain cells


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. 

11 Replies


=SUMIF(A1:A100, 1, C1:C100)


Adjust the ranges as needed (they must be the same size)

@Jamesboden As per your screenshot SUMIFS() should work.





Hey Hans. Thats great thank you. Would you know how to add a date range to it. so 01/08 to 31/08?


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)

This is coding I'm trying to use but I'm getting an error
=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.

@Hans Vogelaar This is very basic idea. As you notice in tab "Costs" its saying 0, but there is data in the data tab. 


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.



               sum_rng, crit_rng1, criteria_1, crit_rng2, criteria2, crit_rng3, criteria3

D'oh! so simple. Thank you so so much