# averageifs

1. ### AverageIfs function only returns first matching value

I have a problem with an excel averageifs formula in that it only returns the first matching value, and does not average all values which correspond to the right criteria. The goal is to average every value which corresponds to a particular hour of the day. I've attached a screenshot below...
2. ### AverageIFS formula where values that have the same identifier need to be counted as 1

I have a dataset of sales data and I need to find the average order value of all customers. If I take the average of all orders the average is lower than expected. This is because a customer will make a large purchase one day and then maybe a smaller purchase a week later and the average formula...
3. ### AverageIFS formula on another workbook

Hi All. I want to pull an average from the last 2 years in another workbook, but it only works when the other workbook is open. The formula i have now is: =AVERAGEIFS(Path..\$BU:\$BU;Path..\$B:\$B; B4; Path..\$F:\$F; ">"&TODAY()-(Q8*360)) BU:BU = Numbercolumn B:B = Materialnumbers B4 =...
4. ### How to Average Only Non-Zero Values Using AverageIfs with Index/Match

Here is my current formula: AVERAGEIFS ( INDEX ( \$H\$10:\$X\$505, , MATCH ( [SKU], \$H\$10:\$X\$10, 0)), \$E\$10:\$E\$505, [City], \$F\$10:\$F\$505, [Store Name]) I am trying to summarize the average prices (H11-X505) by product (header cells H10-X10), city and store (Columns E-F) in a separate table using...
5. ### Excel Formula for Avg Tenure with a twist

Hi I am trying to figure out a formula that would give me the average tenure per year of the Active employees for that year. I have a list of the employees with start and end dates and their Departments, and their tenure for each year they worked. Here are the two lists: What I need is to get...
6. ### AVERAGEIFS Returns #value!

Hi Guys, I trying to get the Avg results for each employees, but always return #Value! I'm using this function: =AVERAGEIFS('Evals Report BR'!I:I,'Evals Report BR'!P:S,PowerBI!B2) Maybe is because I selecting 4 columns in criteria range? But if so, any function I could be using for that...
7. ### Averageifs returns #div/0 using a date range

I am trying to find the avg of a value base on a range of dates. In column A i have the dates (there are empty cells in this column", column C I have the value i want to AVG. i am using the following formula =AVERAGEIFS(C:C, A:A, "<= 1/1/27", A:A, ">= 1/1/2021"), but continued to get a #/div/0...
8. ### AVERAGEIFS with time as criteria

I’m trying to get an average of data in column C based on the corresponding time range in column A. The input in columm A is time. So for example, say I want the average between 09:00:00 and 09:30:01. In Column N2 and NO I put those 2 times respectively. The formula I made was the following...
9. ### Days between in- and outgoing for a total average.

Hello everyone, I`ve been going absolutely mad with an issue I just can't seem to get my head around. I'll try to give an example to best detail the issue on hand: Imagine I get a penalty fee by having a too fast of a turnover of the cattle I trade, so If I re-sell the first day it's 17% fee...
10. ### How to reference data in a formula

Hi, I want to use an AVERAGEIFS formula and have a long list of criteria. Is there a way to do the calculation and reference a table that has mapped the criteria to one cell? If I want to calculate the average sales for Part-time employees in that work in London Stores (Zone 1, zone 2...
11. ### Excel problem

Hi guys can you please help me in doing points: 1,2 and 3 of the assignment here below: (I also added below the excel file)
12. ### How to use Calculate average with multiple criteria and ignore text

Hello all, i want to calculate the average refer the contractor name and ignore the text "NA". I know that it needs to use averageifs to calculate but it shows the #DIV/0! error if the data contain text or no text Would you please tell me how to fix it? I had wrote down my formula and the...
13. ### Average if there is a count above 2

Hi all, Hoping for some help with a formula please. I have an averageifs formula that works fine looking for an average with two criteria. My issue is I only want this function to work if a name appears more than 2 times for everything else I just want it to be blank. I have uploaded an...
14. ### Averageifs within a Table

Hi All, This has probably already been answered on here but I have tried a few different solutions and not managed to get what I am looking for. I have a Table with Days in D1:AE1, Dates in D2:AE2 & Intervals in C3:C29. Then I have the relevant data in each cell based on that time and date. I...
15. ### Average all values in two different columns that are between two values?

Hello all, I am trying to get an average of values in two non-consecutive columns, and only include values between 80 and 120. In the screenshot below, I would like to average the values in columns I and K together. I also need to exclude any "" cells and cells that may contain #N/A. My full...
16. ### averageifs in filtered table

Hi, I am trying to calculate the average from a table using the averageifs function, because I only want to take into account the numbers between the lower and upper boundary (LB and UB). I know I need to use the subtotal function to not take into account the hidden cells. I could not find...
17. ### calculating average with multiple criteria and removing outliers

Good afternoon, I have built with power query a list of payments made for the last 4 years to employee's. I am trying to calculate the bi-weekly average salary of those 10 000 employee's based on 2 criteria's. For each employee / fiscal year, I want to find the average bi-weekly payment. I want...
18. ### Use AVERAGEIFS based on dynamic criteria

I'm trying to average a set of cells based on multiple criteria (hence the AVERAGEIFS), but I want this average to change as more data is added. There may be a very simple fix for this, but I can't figure it out for the life of me. Below is a sample of the table followed by an explanation of...
19. ### Help with a complex AVERAGEIFS formula

I have the below SUMIF formula that works in my spreadsheet that I would like to modify to AVERAGEIFS based on all of the same criteria minus the column to Average. I tried to sub out SUMIFS with AVERAGEIFS but all I get is a #DIV/0! error. Not sure why I am getting the error. I have also tried...
20. ### AVERAGEIFS not giving expected value

Hi, I am trying to do an AVERAGEIFS formula but the provided answer is not correct. Current formula I have is =AVERAGEIFS(D:D, B:B, ">="&A2, C:C, "<="&A2) I have a long list of sequential dates in Column A, column B is the date a device was sent (or today if not sent), column C the date it was...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back