Forum Discussion

PeterYac's avatar
PeterYac
Copper Contributor
May 01, 2020
Solved

SUMIFS - Can criteria be entered directly?

Hi, I have a sumifs like:   Sumifs(A1:A4,Z1:Z4,3) where Z1:Z4 contain the number 1,2,3,4   I was trying to do this like this:   Sumifs (A1:A4,{1,2,3,4},3)  thereby removing the need for the ra...
excel
Excel for web
  • PeterYac's avatar
    PeterYac
    May 01, 2020

    SergeiBaklan

     

    Hi Sergei,

     

    Thanks for this - your first suggestion works fine.

     

    When I tried SUMIFS(A1:A4,{1,2,3,4},3) I just get a syntax error - Excel clearly does not like {1,2,3,4} in this formula but evaluates it OK in the SUMPRODUCT one. Strange?

    thanks again

     

    Peter

     

     

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 01, 2020

PeterYac 

I didn't catch entire idea, perhaps that could be done another way, e.g. using CHOOSE(), but if literally when

 

=SUMPRODUCT(A1:A4*({1;2;3;4}=3))

 

or

 

=SUMPRODUCT(A1:A4*(ConstantsList=3))

 

if you name above array in Name Manager 

  • PeterYac's avatar
    PeterYac
    Copper Contributor
    May 01, 2020

    SergeiBaklan

     

    Hi Sergei,

     

    Thanks for this - your first suggestion works fine.

     

    When I tried SUMIFS(A1:A4,{1,2,3,4},3) I just get a syntax error - Excel clearly does not like {1,2,3,4} in this formula but evaluates it OK in the SUMPRODUCT one. Strange?

    thanks again

     

    Peter

     

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 01, 2020

      PeterYac 

      Peter, SUMIFS requires the range as argument, SUMPRODUCT works both with ranges and arrays.