averageif from another sheet

Lila De Vera

Hi everyone,

I have a sheet that is populated from a form. There are several departments listed, and more often than not a department is listed more than once. Within that sheet I have a column listing the response time in days. The response time in days is calculated based on the information within that row.

I have another sheet for statistics that is used to populate my dashboard. I'm trying to calculate the average response time in days per department. I thought the averageif function would work but it's not. I've attached the two columns in question and the formula I'm using at the time. Thanks!

I should note that the current formula displays 0, however, it should be 3.

David Tutwiler

Ah! I think I have it. I tried it all on a local sheet to keep me from getting distracted with the references.

I think the problem is that your original range is looking at the numbers to sum up, instead of looking at the column that contains your search term. I think you need to formulate it where you look at your search term for the first two parts of the AVERAGE or SUM, and then point it to the range to AVERAGE or SUM at the end. Something like this:

=AVERAGEIF({Master Sheet Range 6}, "Agriculture and Resource Development", {Master Sheet Range 3})

I think that will work.

John Jonassen

Try changing your equals sign to a comma?

David Tutwiler

I think the comma will move the text to the Range and you'll get unparseable. The only think I can think of would be to change your range names and re-state them and try again. Occasionally I find that the ranges (especially if they are large) have issues from time to time.

Lila De Vera

Hi @David Tutwiler, for some reason that didn't work either. I tried to do a number of columns for the issue now. A column that counts the department (countif) and another column to sum the number of days if the department is X (sumif). But now the sumif is returning as 0 when it really shouldn't. bizarre.

David Tutwiler

@Lila I'm curious if you copy the formula down, does it return a 0 for all Departments, or just the Agriculture?

Lila De Vera

@David Tutwiler it returns 0 for all departments. I thought that because two of the rows are blank it may affect the formula, when I did input a digit it had no affect. I'm stumped.

David Tutwiler

Ah! I think I have it. I tried it all on a local sheet to keep me from getting distracted with the references.

I think the problem is that your original range is looking at the numbers to sum up, instead of looking at the column that contains your search term. I think you need to formulate it where you look at your search term for the first two parts of the AVERAGE or SUM, and then point it to the range to AVERAGE or SUM at the end. Something like this:

=AVERAGEIF({Master Sheet Range 6}, "Agriculture and Resource Development", {Master Sheet Range 3})

I think that will work.

Lila De Vera

@David Tutwiler amazing, thank you so much. Works like a charm.

David Tutwiler

Awesome! Glad it works now.

Haley C.

@David Tutwiler thank you for this!! It solved a problem for me as well.

David Tutwiler

Great to hear @Haley C. The community is a great place to find these types of answers.