Forum Discussion

MarcoTorinoi's avatar
MarcoTorinoi
Copper Contributor
Mar 17, 2024

SUMIFS with OR

I have this list:

Column A      Column B

Austria            10

France             20

Italy                 30

China               10

USA                 40

 

I want to sum up all the cells  in Column B if in Column A there is a European Country

=SUM(SUMIF(A:A,{"Austria","France","Italy"},B:B))

The result is correctly: 60

 

Now, however, the list of the European countries is much longer and I do not want to include all of them in the Array, but I want to refer to other cells where I have the list of all European countries. 

 

Is there a way to do it? I think in the Array it is not possible to put a reference to a cell range.

 

Thanks!!!

 

Marco

 

 

Formulas and Functions
  • djclements's avatar
    djclements
    Mar 17, 2024

    MarcoTorinoi Did you see my note about pressing Ctrl+Shift+Enter? If you're not using Excel 2021, Excel for MS365 or Excel for the Web, you'll need to press Ctrl+Shift+Enter when inputting that formula. Alternatively, you can also swap out SUM with SUMPRODUCT without the need for CSE:

     

    =SUMPRODUCT(SUMIF(A:A, D2:D4, B:B))

     

    Here's a couple of screenshots using Excel 2010. Note: the curly brackets are added automatically after pressing Ctrl+Shift+Enter (do NOT type them manually)...

     

    Results (Excel 2010)

  • djclements's avatar
    djclements
    Bronze Contributor
    Mar 17, 2024

    MarcoTorinoi Don't use the curly brackets at all... just reference the range containing the list of European countries. For example, if Austria, France and Italy were listed in range D2:D4, the formula would be:

     

    =SUM(SUMIF(A:A, D2:D4, B:B))

     

    Note: with older versions of Excel, you may need to press Ctrl+Shift+Enter when inputting this formula.

     

    Results

      • djclements's avatar
        djclements
        Bronze Contributor
        Mar 17, 2024

        MarcoTorinoi Did you see my note about pressing Ctrl+Shift+Enter? If you're not using Excel 2021, Excel for MS365 or Excel for the Web, you'll need to press Ctrl+Shift+Enter when inputting that formula. Alternatively, you can also swap out SUM with SUMPRODUCT without the need for CSE:

         

        =SUMPRODUCT(SUMIF(A:A, D2:D4, B:B))

         

        Here's a couple of screenshots using Excel 2010. Note: the curly brackets are added automatically after pressing Ctrl+Shift+Enter (do NOT type them manually)...

         

        Results (Excel 2010)

Resources