Forum Discussion
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
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)...
- djclementsBronze Contributor
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.
- MarcoTorinoiCopper Contributor
- djclementsBronze Contributor
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)...
- SandeepMarwalBrass Contributor