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

istock_000068648683_large.jpg

Paperwork
kitzcorner_iStock_paperwork
Share this content
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.

Replies

Please login or register to join the discussion.

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)

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)

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)

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

Thanks (0)

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)

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

Thanks (0)

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)

rafshan

Thanks (0)