How can I perform functions on a specified subset of rows?

excel worksheet-function gnumeric

user597720 · Dec 19, 2013 · Viewed 11.7k times · Source

I want to find the max (min, average, etc.) of a column but only for the subset of rows where another column matches a certain pattern.

For example, here is the data in the sheet named "data":

Date   Value
Jan    15
Jan    17
Jan    3
Feb    19
Feb    34
Feb    37

Then in a separate sheet, "reports", I'd like to have:

       Jan    Feb
Max    17     37
Min    3      19

What function I can put in the cells in the "reports" sheet to get those values?

If it matters, I'm using Gnumeric 1.10.

Answer

To achieve:

=max(column b where column a == Jan)

You can use an IF to do that:

=MAX(IF(A2:A7="Jan", B2:B7))

This formula should be entered as an array formula however. In excel, you do this with Ctrl+Shift+Enter.