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

     

     

     

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.