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 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