averageif from another sheet
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.
Best Answer
-
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.
Answers
-
Try changing your equals sign to a comma?
-
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.
-
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.
-
@Lila I'm curious if you copy the formula down, does it return a 0 for all Departments, or just the Agriculture?
-
@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.
-
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.
-
@David Tutwiler amazing, thank you so much. Works like a charm.
-
Awesome! Glad it works now.
-
@David Tutwiler thank you for this!! It solved a problem for me as well.
-
Great to hear @Haley C. The community is a great place to find these types of answers.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!