Forum Discussion
SUMIFS with OR
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)
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
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)
- Thanks. the solution with SUMPRODUCT works , ...but since I use the formula more then once in the same worksheet, the calculation is super slow and it freeze the screen for 10 or more seconds for every refresh... 😞
MarcoTorinoi The performance issues are caused by referencing entire worksheet columns (A:A & B:B), which means SUMIF has to process 1,048,576 rows of data for every use of this formula. Try reducing the range according to the size of your dataset (ie: A1:A1000 & B1:B1000) or format your dataset as a structured Excel table (on the ribbon, go to Insert > Table). When referencing an Excel table, the formula would look something like this:
=SUMPRODUCT(SUMIF(Table1[Country], D2:D4, Table1[Amount]))The built-in column names of an Excel table are dynamic and will expand automatically as the table expands.
Excel Table Column References