# Formula - Average Values That Match Criteria

Jan 3, 2008

The attached workbook has two tabs:
1. Burn Rate - this is where I need my formula to calculate
2. prorder - this is where the table will be

What I need:

1) from 'Burn Rate', get the 'PO ID' we will look up in the table.
2) go to 'prorder' - when the 'PO ID' there matches that same 'PO ID' from the other worksheet - take the value in column F - this is the number that will be averaged.

In other words - in 'Burn Rate', for a given 'PO ID' (column A), I want to average all of the values that are found in column F in 'prorder', and return that average to 'Burn Rate' (column B).

## Generating Average From Group Of Numbers In List That Match Two Criteria?

Jan 29, 2013

So we had a month long, company paid (woohoo!) "weight watchers" challenge. On 1 worksheet ("Stats"), I have the Name of every Employee (A Column), then their Start Weight (B Column), End Weight (C Column), Department (D Column) and finally Location (E Column).

I know how sensative some people can be about their weight, so I locked the page and created another worksheet named "UI", which will display the "Average End Weight" for each department. So Human Resources would be the department in cell A2 on the UI sheet, with City 1 being the Column Header in cell B1. There are 8 cities (offices) and 23 departments.

So, for cell B2, I want to scan through the "Stats" worksheet and locate all of the cells in the department and location columns that read "Human Resources" and "City 1" respectively. Then grab the "End Weight" for every row that meets these 2 criteria, add them up, and divide by the number of "End Weights" that were grabbed.

My solution would be to make a separate column for each department (and then each city, so essentially 8 columns to represent all the cities for each department), use a nested (maybe 4) IF statements to then list only the end weight if that particular row meets the criteria, then at the bottom of each separate column, add all the shown numbers up and divide, then draw the information from THAT number into the "Average" cell on the UI worksheet. But it isn't very dynamic and if i want to use this sheet next year, there will be more employee info to be added and it would be a mess..further more, it would be...what...23 Departments x 8 Cities x 155 employees = ~28,500 added cells.

## Average Values That Correspond To Certain Criteria?

Jan 23, 2014

I have 4 columns of data, and need to average values that correspond to certain criteria from the other 3 columns. Attached is an example of my data and the desired output. The averages need to come from non-blank cells from Inputs 1 and 2 that match the criteria from the Name column.

## Match Multiple Occurrences And Offset - Then Average All Values Found

Nov 11, 2013

Attached is a sample file.

I can't figure out a formula that will match either 1,2,or0 in column K and offset to corrosponding value in column B. Then average of all values that came up.

how to accomplish this. VBA is acceptable, but formula is prefered.

0.644
0.627
0.641

[Code]....

## Excel 2003 :: Average Of Values Across 5 Sheets With Two Criteria

Jun 12, 2014

I have a couple of issues and if its okay will post two threads to make it easier to follow due to my basic skills in Excel.

I have a workbook with 5 sheets. The first sheet is a stat sheet which picks up data from four other spreadsheets. I want to work out the average working days taken to complete a case but this data is across multiple sheets.

So for example Column E in each sheet tells you what type of case it is i.e. investigation. Then column T tells you number of workings day it took to complete the case. To work this out I have used the following formula:

=SUM(SUMIF(Further_Action!E:E,"Investigation",Further_Action!T:T),
SUMIF(Court_Application!E:E,"Investigation",Court_Application!T:T),
SUMIF(No_Further_Action!E:E,"Investigation",No_Further_Action!T:T),
SUMIF(Closed!E:E,"Investigation",Closed!T:T))/SUM(COUNTIF(Further_Action!E:E,"Investigation"),
COUNTIF(Court_Application!E:E,"Investigation"),COUNTIF(No_Further_Action!E:E,"Investigation"),
COUNTIF(Closed!E:E,"Investigation"))

I know its very basic but its the only way I know how to work out an average in Excel 2003. I need to add a second criteria where on every sheet it looks in column G for date the case was allocated. As I'm only looking to report in the current report year I want it to look for cases where the value in column G is =>01/04/14 but =<31/03/14.

## Array Formula For Multiple Criteria Average

Jul 9, 2014

I need an array formula that can take average based on multiple criteria present row wise and column wise.

Check the attached sheet for detail. I need array formulae in yellow space. I have given one result that I need.

Average.xlsx‎

## Formula For Getting Average Of Values Without Counting Zero Values In Range?

Dec 23, 2011

Is there a formula that would allow you to take the average of all values within a range but not count the zero values? I thought something like this might work but it's not. Neither one worked.

=AVERAGEIF(\$E\$4:\$E\$34,">0")
=AVERAGEIF(E4:E34,">0")

## Build Average Formula Based On Changing Criteria

Oct 16, 2011

Objective: to create an average variance among exact match RevCode

Example: all RevCodes with "666" should be used to make an average variance statistic

Situation: All RevCode's are not in similar group counts and this creates incorrect averages with a standard formula.

Set of Data

RevCode Observed Expected Variance RevCodeMean
666 200 220 -9.091%
666 205 220 -6.818%
666 207 220 -5.909%
777 500 355 40.845%
767 505 505 0.000%
668 100 105 -4.762%
668 105 105 0.000%

Formula that generates average variance between exact match revenue codes:

=(SUM(B2:B4)-SUM(C2:C4))/SUM(C2:C4)

The solutions can be in VBA or within the spreadsheet either way.

## If Match Then SUM? - Wanting To Add Multiple Values If Criteria Matches

Mar 26, 2009

Column A would be Recipe Numbers, Column B the Planned Total.

I want to search column A for all matching recipe numbers.
Then, any recipes that match, look in column B for the planned totals, adding all incidents where they match.

Then the most difficult part, which just occured to me now.
I only need the Total Planned Total and Recipe name reported once.

Recipe#..........Planned Total
450................100
600................75
620................125
450................50
620................180
450................100
600................200
450................110

I will not be able to sort these lists.

If-Match-Sum? to display:
Recipe#............Total Planned Total
450....................360
600....................275
620....................305

I've researched for the first part... and it seems like Match will only find the first match and not look further? Am I wrong on this?
How to display the results hadn't even occured to me yet, since I was still trying to figure out how to GET the result.

## Match With Multiple Criteria To Dataset Of Unique Values?

Jul 8, 2013

In one sheet I'd like the user to select from 3 dropdown lists certain predefined values.

On the second sheet there is a long list of unique cells (one column, that can't be split into usefull columns with text to columns or something). I want to find the cell that holds the three text choices. These can be in different order to make things more complex. How do I create a search that finds that one match.

Example
The user selects "AAA" and "DDD" and "FFF" from the dropdown lists

The formula should find that one cell that holds this value: "FFF JJJ GGG DDD CCC AAA". This is the only cell that holds all three chosen values in one text. In the end I would need to have the row number of that cell,

## Return Multiple Values That Match Single Search Criteria?

Nov 28, 2012

Attached is a sample workbook, but essentially what I'm looking to do is automate the process of searching through a data set where the value of interest (in this case, names) often has multiple entries, with different values attached to each instance.

I would like to be able to get a list of all values in a given column that match a specific name in another column.

Currently I'm using a basic INDEX/MATCH search just to see whether the data exists at all, but that's only half of what I have to do here, and I'm totally stumped on how to get a comprehensive list of all matches.

For reference, if you look at the sample, what I need is a list of all values in the "CPT" column that match the name searched for in the first column.

The actual data set size is at most 3-400 entries, if that makes a difference in how to approach this.

CPT Sample Book.xlsx

## Formula Average The Values

Apr 4, 2007

I am trying to do a stock ( goods) inventry for a catering industry, meaning most good swill keep havinf cost prices changing.

I am trying to use a LIFO system. To achieve the end result, i am trying an average formula and i am trying something as follows:

=SUMIF(I4:I20,">0",I4:I20)/COUNTIF(I4:I20,">0")

To try it out, i created a similar page with the same amounts but using a normal multiplication and division formula, the totals dont marry.

## Formula To Sum Multiple Match Criteria?

Jan 28, 2014

I am needing a formula to sum info from 1 book to another based on two lookup criteria. Its the sort of info you can easily get with pivot tables or filters but my colleague needs a copy and pastable formula so it can be automated.

There are two workbooks Order Index example.xlsxCash Flow book example.xlsx; one called "Order Index" has a list of orders placed along with supplier name (Col C), date due for payment (Col H) and value of order (Col I). The other workbook, used by a colleague is called "Cash Flow" and contains a list of supplier names (Col B) with row 1 containing months (1st Jan 2014, 1st Feb 2014 etc).

Under each month on the "Cash Flow", I need to show the sum of orders due for payment within that month, for each supplier named (in Col B). In other words, sum the figures from the "Order Index" based on supplier name and date due for payment.

Often, on the Order Index there is more than one order per month from a supplier and the dates due are specific days (10th Jan, 21st Jan etc) and these need summing up for the 1st Jan 2014 Column in the Cash Flow book.

## Copy Formula Down If Criteria Match

Sep 18, 2006

I need to copy down formulas from Row 4 to all rows below til end if value Col F > 0.

All other data on subsequent rows must remain intact.
Need to loop through all shts in the wb

Guess where I'm stuck, I'm not sure how to setup the destination range for the paste

Here's where I'm at so far
Just a snippet, not complete:

Sub Evaluate_Paste_Formulas()

Dim wbBook As Workbook
Dim sht As Worksheet
Dim rngCopy As Range
Dim rngDestination
Dim lngRows As Long

## Index Match Formula For Multiple Criteria?

Dec 3, 2013

In the attached I have a pricing list on sheet 2 based on various criteria and on sheet 1 dropdown lists to match the criteria.

In cell B12 i have an index match that I can not get to return a value.

Is Index Match the correct method of returning a result? Or have I just got the formula wrong?

## Multi Criteria With MATCH Or LOOKUP Formula?

Mar 20, 2014

What formula should I put in NUTRISTATUS column. That will search value in the table of MALE BMI and FEMALE BMI. for example when I input data M(male) in ***(gender), age in M(m0nth) and BMI it will search in MALE BMI table..

and I input data F(female) in ***(gender), age inM(month) and BMI it will serach in FEMALE BMI table.

and if the criteria match will appear the word SEVERLY UNDERWEIGHT or OVERWEIGHT or UNDERWEIGHT or NORMAL or OBESE in NUTRISTATUS Column..

## Index Match Formula With Multiple Criteria

Feb 5, 2013

Is it possible to use an Index/Match formula that looks at multiple criteria?

## Index & Match Formula: Multiple Row Criteria

Jun 20, 2007

I am trying to get my INDEX & MATCH formula to retreive data from my table.

This is what I can do so far:
Jan-07Feb-07
100 12
250 45

=INDEX(table,MATCH(B13,balance),MATCH(C13, date))

But I am trying to get it to get another row to look up as well.
I want it to look up the color then the 100 or 250, then the date.
Jan-07Feb-07
red100 12
red250 45
blue100 78
blue250 1011

=INDEX(table,MATCH(B13,balance),MATCH(C13,date))
I think i need to insert another match code in the row section but cant seem to get it to work.

## Formula To Delete Column If It Doesn't Match Criteria

Jun 2, 2009

I have a column of cells (Column I2:I1063) with zip codes in it and I want to keep the row if the zip code matches one on the list in a column B2:B100 on a separate sheet (Sheet1).

## IF Formula - Get Average Of Only Values In That Range Not Including Blanks

Nov 21, 2011

I am looking for a formula for the following:

If there is nothing is U6:W6 return blank

Otherwise sum the contents of Bf6:BH6 and divide by the number of cells that are not empty in the range U6:W6 (to get an average of only the values in that range not including blanks).

## Array Formula To Match Closest Dates Based On Another Criteria

Jul 18, 2012

I am trying to run an array formula to match two dates on two seperate sheets as close together based on another cell. Below is what I currently have on some made up values. I am entering it as an array and ideally I would like it to run down the cells changing the reference A2 depending on which cell it is then to then enter this into a macro.

The formula I am currently using is:

=INDEX(Trees!B\$2:B\$75,MATCH(MIN(INDEX(ABS(IF(A2=Trees!A\$2:A\$75,(Trees!B\$2:B\$75-B2))),0)),INDEX(ABS(IF(A2=Trees!A\$2:A\$75,(Trees!B\$2:B\$75-B2))),0),0))

Sheet 1 (Named: Main)

Number
Date
Formula

1
15/06/2012

[Code] ....

Sheet 2 (Named: Trees)

Number
Date

1
05/06/2012

[Code] .....

## Reverse VLookup (Index Match) To Return Multiple Values Based On Single Lookup Criteria

Jul 11, 2012

I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.

## Create Formula That Creates Average But Excludes Top 10% And Bottom 10% Values?

May 5, 2014

how to create a formula that creates an average but excludes the top 10% and bottom 10% values?

## Formula: Average Value Of Cells Left Of Highest N Values In Above Column Range

Sep 24, 2006

I need a cell formula that will (a) identify the highest N values in an above specified column range, (b) color the interior of those N cells (I suspect that this is not possible), and most importantly (c) return the average value of N corresponding cells, where the corresponding cells are located on the same rows as the identified N high value cells but in a specified column to the left (not necessarily adjacent)

Does anybody know what this formula would look like?

Example:

-----------------------
...| A | B | C | D | E | F |
-------------------------
1 |....| * |.........| 7 |...
-------------------------
2 |....................| 2 |...
-------------------------
3 |....| * |.........| 6 |...
-------------------------
4 |....................| 1 |...
-------------------------
5 |....| * |.........| 5 |...
-------------------------
6 |....................| ? |...
-------------------------

? = average of B1,B3,B5 where (N = 3) and (specified column to the left = B)

## Match Formula Returning Values

Jan 27, 2014

I have two tabs (In-Transit) & (AC) column A in both sheet have a "Concatenate" that i copied and pasted and values only and also switch the format to "Text" on both tabs. I do a vlookup

[Code] ......

and it returns the results i want....But then i was thinking due to the volume of data what if the value in column A is found twice on tab "AC".. i wouldn't know with a "Vlookup".

So trying to build a Index/Match formula
[Code] ....

I also returns some good results, but i also get some back as #REF. I also get #NA and both of the formulas and those are valid no matches...

## Using LARGE Formula With Index / Match To Pull Values When There Are Duplicates

Sep 13, 2012

I am attempting to use the LARGE formula to pull the top 3-5 percentages out of a field of 50-100, while using the INDEX/MATCH function to pull the corresponding "descriptor" that is associated with those top 3-5 percentages. (I need to do this across multiple data sets, but I can't get past this 'duplicate' issue) However, I am running into the problem when there are 2 percentages that are identical (WH 14 and WH 16 in pasted text below), then the INDEX/MATCH function only pulls the 1st "descriptor" and doesn't continue down to the Duplicate. how to tell excel to move to the next set of duplicate data and match the 'descriptor' to that data?

I have attached a file that should show what I am trying to do. These are the formulas I am using right now, pulled down into the 3 cells below them to get the top 3.

B1:

[Code]....

C1:

[Code]....

Warehouse S/S %
WH 1 50.00%
WH 2 57.14%
WH 3 0.00%
WH 4 50.00%
WH 5 100.00%
WH 6 60.00%
WH 7 33.33%
WH 8 66.67%
WH9 60.00%
WH 10 63.64%
WH 11 78.57%
WH 12 55.56%
WH 13 42.86%
WH 14 71.43%
WH 15 61.54%
WH 16 71.43%

Attached File: Book1.xlsx‎

## Formula- Sum Values In Col H Based On The Criteria Set In CELL B1

Oct 3, 2009

i need a formula that will sum values in Col H based on the criteria set in CELL B1. The tricky part is that i need the summed values to be converted using the FX rates in Cell M:N, i have provided an answer on what the formula should return in CELL C2....

## Formula To Find Matching Values In One Column And Return Totals For All Rows That Match

Dec 26, 2013

table1.jpg

I am looking for a formula that will satisfy the following:

1) find all the values in column "A" that match
2) In column "G", sum up all the values in "F" that go with the matching values in column "A"
3) For example, in rows 14-16, the values in column "A" match. Cell G16 sums up F14:F16

## Formula To Sum Values From Column Based On Multiple Criteria

Nov 29, 2013

I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.

What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.

Sample Workbook.xlsx‎

## Array Formula To Return Multiple Values Based On Various Criteria?

Dec 19, 2013

I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....

My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)

I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.