AVERAGEIF Formula

Kevin Rice

I am trying evaluate an entire column of data and average data in that column based on date entries in another column and have been having some difficulties doing that. I've been trying to achieve this using the AVERAGEIF formula something else embedded in there but haven't had any luck.

Genevieve P.

Hi @Kevin Rice

AVERAGEIF is a great function if you only have one criteria to search for (ex, your date is greater than Today). However, if you have more than one criteria, you'll want to use a combination of AVG and COLLECT.

Here's an example of an AVERAGEIF formula with a date:

=AVERAGEIF([Date Column]:[Date Column], >TODAY(-30), [Data Column]:[Data Column])

This will average the cells in the Data Column that correspond with a row where the Date in the Date Column is greater than 30 days ago. I used the TODAY function to indicate 30 days ago (-30).

Here's an example of an AVG(COLLECT to look for more than one criteria:

=AVG(COLLECT([Data Column]:[Data Column], [Start Date]:[Start Date], <TODAY(), [End Date]:[End Date], >TODAY()))

You'll notice that with AVG(COLLECT you first list the column that you want to average, then you list the columns & criteria afterwards (opposite to AVERAGEIF).

If you're still having trouble figuring out your specific formula, it would be helpful to know exactly what date criteria you're looking for. It would also help to see a screen capture of your sheet, but please block out any sensitive data.

Cheers!

Genevieve

Kevin Rice

What I'm looking to do is evaluate an entire date column and average values based on whether or not they are in a month. For example, in January I want it to average data based on if the dates are greater than/equal to 1/1/2020 and also less than/equal to 1/31/2020.

Please see the attached picture below of an example of the information I'm looking to evaluate. I'm not so much worried about the individual times you will see in there, just the dates (Row "A") and then the numbers near 1700-1800 (Row "C" we can call it).

Genevieve P.

Hi @Kevin Rice

Thank you for clarifying! In this instance, you could actually use AVERAGEIF because you have one criteria: what month is in that column. Instead of looking between two dates, you can use the MONTH function to search for a specific month in a date range.

Try this:

=AVERAGEIF([Date Column]:[Date Column], IFERROR(MONTH(@cell), 0) = 1, [Data Column]:[Data Column])

This first lists the Date column to evaluate, then the criteria. The criteria in this case is that the MONTH in each cell (@cell) is equal to January (equal to 1). I wrapped this in an IFERROR to clear out any blank cells.

Then you can just swap out the number after the MONTH to be the different months... so for February it would be 2, or the following:

=AVERAGEIF([Date Column]:[Date Column], IFERROR(MONTH(@cell), 0) = 2, [Data Column]:[Data Column])

Now, where this might get tricky is if you have multiple years in there, as well. If you want to only find data from 2020's January, then this is when you'd use AVG(COLLECT instead, add in a YEAR statement, and do the following:

=AVG(COLLECT([Data Column]:[Data Column], [Date Column]:[Date Column], IFERROR(MONTH(@cell), 0) = 1, [Date Column]:[Date Column], IFERROR(YEAR(@cell), 0) = 2020))

Let me know if either of these work for you!

Cheers,

Genevieve

Kevin Rice

Would it also make a difference if these are on a separate sheet? This formula will be a cross-sheet reference. The value I got back after typing:

=AVERAGEIF([Date Column]:[Date Column], IFERROR(MONTH(@cell), 0) = 1, [Data Column]:[Data Column])

Was "0".

Kevin Rice

I apologize, I had some oversight on this one and wrote a formula where the expected result would indeed be 0. 😂

I re-wrote it where it actually had some data to look at and it worked. Thank you very much!

Genevieve P.

Hi @Kevin Rice

I'm glad you got it working!

To answer your original question though, it would make a difference if this was a cross-sheet formula. The references I built in were for the same sheet, so I referenced a column [like this]:[like this].

You will need to use a cross-sheet reference to find each column and reference them {like this} instead.

Ex:

=AVERAGEIF({Date Column in other sheet}, IFERROR(MONTH(@cell), 0) = 1, {Data Column in other sheet})

Cheers,

Genevieve