SUMIFS

Try using mapflo for data analysis instead of Excel. Get Started for Free.

Summary

With the SUMIFS function, you find a match for a value in both column A and separate value in column B etc. and return the sum of the values in column C that match.

Syntax

=SUMIFS(“output_column”, “look_up_column_1″,”value 1”, “look_up column_2″,”value 2″, etc.)

In the example below, the formula in cell E2 is =SUMIFS(C2:C7,A2:A7,”Shorts”,B2:B7,”L”) and returns the value 24 as the Qty associated with Item:”Shorts” and Size:”L” in the range C2:C7 is 24.

Notes:

• SUMIFS is not case sensitive.
• SUMIFS can reference:
  • a number: =SUMIFS(C2:C7, A2:A7, “T-shirts”,C2:C7,13) returns 13
  • a string: =SUMIFS(C2:C7,A2:A7,”Shorts”,B2:B7,”L”) returns 24
  • a cell reference: =SUMIFS(C2:C7,A2:A7,A7,B2:B7,B7) matches to Sandals and M and returns 15
  • a math operator: SUMIFS(C2:C7, A2:A7,”T-shirts”,C2:C7,”>5″) returns 19 (matches to cells C2 and C5)
  • multiple wild characters: =SUMIFS(C2:C7,A2:A7,”S*”,B2:B7,”L”) matches to Sandals and Shorts, Size L and returns 40 [where * denotes zero, one or multiple characters]
  • single wild characters: =SUMIFS(C2:C7,A2:A7,”Sh?rts”,B2:B7,”L”) matches to T-shirts and Shorts, Size L and returns 37 [where ? denotes a single character]

Using mapflo with SUMIFS

If you like Excel, then you’ll love mapflo. mapflo puts data through a flow. The Rank & Partition Node makes it easy to SUMIF, RANK, COUNTIF, AVG, MINIMUM or MAXIMUM:

You can SUMIFS by partitioning by more than one column. In the example above you would partition by Item and Size.

No copying down formulas; no worrying about a change in dataset size.

Get started with mapflo for free.