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

kitzcorner_iStock_paperwork
Tags

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

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

### Replies

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)
By David Ringstrom
to 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)
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)
to Rosario Manahan
Jun 26th 2015 01:11

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

Thanks (0)
By David Ringstrom
to 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)
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)
By David Ringstrom
to Toronto Accountant
Jun 26th 2015 01:11

Ronald,