Contributor

# 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

# Re: Formula for adding certain cells

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

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

# Re: Formula for adding certain cells

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

``=SUMIFS(H:H,A:A,1)``

# Re: Formula for adding certain cells

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

# Re: 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)

# Re: Formula for adding certain cells

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)

# Re: Formula for adding certain cells

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)

# Re: Formula for adding certain cells

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.

# Re: Formula for adding certain cells

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.

# Re: Formula for adding certain cells

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

# Re: Formula for adding certain cells

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

# Re: Formula for adding certain cells

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