sumifs with multiple criteria mac excel 2013

Two ranges or arrays is usually what SUMPRODUCT does but it can be expanded to more values. =SUMPRODUCT(B2:B6,C2:C6) multiplies each value in the first range (Column B) with the matching value in the second range (Column C) and adds up the results.Īs you can see, the SUMPRODUCT result is the same as the total of all the individual stock values in Column D. This is a handy function shortcut for a common need – to multiply sets of numbers then add up the results. An error cell (#N/A) will cause an error in SUM.

=SUM(B3 * PI(),B2) takes the value in B3 multiplies by PI (3.14159…) then adds the value in B2.īut what if one of the cells has text instead of a number? A cell with text, logical or empty are ignored by SUM. SUM can add up references to cells, range, named range, formula or array.Īllowing formulas gives you flexibility to SUM without needing an intermediate cell to calculate before adding up. You can mix ranges and single cells eg SUM(B2:B5,CD30). Up to 255 individual references are possible. Use a comma to separate cells you want to add up eg SUM(B2,B23,CD30) adds up the single cells B2, B3 and CD30 (in a large worksheet). SUM(B2:B5) will add up the numbers in cells B2, B3, B4 and B5. In this article we’ll look at the options available in SUM and related functions.

There’s SUM and then there are all the useful SUM variations to choose from.Įxcel’s SUM() function is probably the first one we learn but there’s a lot more to it than simply clicking on the button to add up a list of numbers.

Sumifs with multiple criteria mac excel 2013