Forum Discussion

Minedayan's avatar
Minedayan
Copper Contributor
Oct 16, 2023

Using different criteria's in same range in sumifs formula

 

Hi,

 

I want to sum of the F column with criteria's 322, 325, 326 which they are on the A column. So if I'm write the formula like =SUMIFS(Sheet1!F:F;Sheet1!A:A;322), it's working but when I'm write =SUMIFS(Sheet1!F:F;Sheet1!A:A;322;Sheet1!A:A;325), it gives me 0. I want to use just one sumifs formula, so I also tried =SUMIFS(Sheet1!F:F;Sheet1!A:A;OR(322;325;326)). I'm wondering if there is any point that I missed. 

excel
Formulas and Functions
office 365
    • Minedayan's avatar
      Minedayan
      Copper Contributor
      Jun 28, 2024

      Thank you SergeiBaklan,

       

      Can you also help me how I can write a formula into this matrix? I want to write more than one criterion I have selected, not as a value, but by linking them to cells, but Excel does not allow it. For example;

       

      =SUM( SUMIFS(Sheet1!F:F,Sheet1!A:A,{VLOOKUP(C2;U:V;2;0),VLOOKUP(C3;U:V;2;0)}) )

       

      Is there any way to write a formula like this?

       

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jun 28, 2024

        Minedayan 

        Nope, {} is to define array of constants, it doesn't work with formulae. As variant that could be

        =SUMIFS(Sheet1!F:F,Sheet1!A:A,VLOOKUP(C2;U:V;2;0)) + SUMIFS(Sheet1!F:F,Sheet1!A:A,VLOOKUP(C3;U:V;2;0))

Resources