Excel Tip: Multiple criteria SUM, MIN, and MAX formulas

Paperwork
kitzcorner_iStock_paperwork
8

Most Excel users are familiar with the SUM, MIN, and MAX functions in Excel. Used in their traditional fashion, you provide a range of cells to the function to derive a result:

  • =SUM(C2:C6) would add up all values in cells C2 through C6
  • =MIN(C2:C6) would return the smallest value within cells C2 through C6
  • =MAX(C2:C6) would return the largest value within cells C2 through C6

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Share this content

Replies

Please login or register to join the discussion.

avatar
By Kyle
Jun 26th 2015 01:11

in your array formulas, what if $E3 is a value that either represents a project number or wildcard. Can $E3 represent a wildcard that will return everything in your array? This might be useful if $B3 were a pivot report and if ("All") were selected, a wildcard would be returned in $E3 so every project could be counted. If a specific project number were selected, $E3 would return that project number and the sum formula will only capture the array matching that criteria. Any feedback is much appreciated.

Thanks (0)
avatar
By David Ringstrom
David Ringstrom
Jun 26th 2015 01:11

Kyle, I'm sorry that I'm just now seeing this comment. To answer your question, if you wanted to return everything, you'd omit the $E3 portion, i.e. MIN(IF(($A$2:$A$6=G$2),$C$2:$C$6)). Also, if you're using Excel 2007 or later, look at the SUMIFS function, which can be much easier to comprehend and implement than array formulas. However, array formulas are useful when backwards compatibility with Excel 2003 is needed.

Thanks (0)
avatar
By Educa
Jun 26th 2015 01:11

Cany any one help me to use the similar formula in excel to find out

the sum the Row 3 IF row 1 is equal to "SecSch" AND row 2 is equal to Current

Say in excel Row 1 (contains a list of "Sec", "Pry", "RCs"), Row 2 (contains "Current" and "Capital") Row 3 (123 (budget)

Thanks (0)
avatar
By Pradeep
Rosario Manahan
Jun 26th 2015 01:11

Can you describe your problem in details so I can help?

Thanks (0)
avatar
By David Ringstrom
Rob Nance
Jun 26th 2015 01:11

Educa, If you're using Excel 2007 or later, the SUMIFS function allows you to sum values based upon multiple criteria. Based on your description, the formula might look like this:

=SUMIFS(3:3,1:1,"SecSch",2:2,"Capital")

If you're using Excel 2003 or earlier, use SUMPRODUCT instead:

=SUMPRODUCT((A1:Z1="SecSch")*(A2:Z2="Capital")*A3:Z3)

David

Thanks (0)
avatar
By Ronald Miranda
Jun 26th 2015 01:11

Very nice!! try visiting my post I explain in there how to get max value with condition:
http://runakay.blogspot.com/20...

Thanks (0)
avatar
By David Ringstrom
Toronto Accountant
Jun 26th 2015 01:11

Ronald,

Thank you for sharing your post, which is a great companion to this article. I've added your blog to my reading list of Excel resources.

David

Thanks (0)
avatar
By nantamu rafshan
Jun 26th 2015 01:11

rafshan

Thanks (0)