Forum Discussion

Copper Contributor

Sep 08, 2022

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 tha...

Formulas and Functions

MVP

Sep 08, 2022

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)

Copper Contributor

Sep 08, 2022

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.

HansVogelaar
MVP
Sep 08, 2022
Jamesboden

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.
- Jamesboden
  Copper Contributor
  Sep 08, 2022
  HansVogelaar This is very basic idea. As you notice in tab "Costs" its saying 0, but there is data in the data tab.
  Book2 - Test.xlsx10 KB
  - HansVogelaar
    MVP
    Sep 08, 2022
    Jamesboden
    
    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

Resources