Free Microsoft Excel 2013 Quick Reference

Average if multiple criteria and contains

I am trying to find the average units sold on one sheet (sht1) of the data on a second sheet(sht2). Does anyone know how i could do this by year="2000" and Car="Contains 2DR"?

I am sort of a novice with excel and do not fully understand the if functions. So any explanation or help would be greatly appreciated.

Year 2000 2Drs Avg Sold:
Year 2000 4Drs Avg Sold:

Car Year Units Sold
Ford Focus 2dr 2000 2
Chrystler 300 4dr 2000 3
Saturn ion 2dr 2000 2
Honda Civic 2dr 2004 1


Post your answer or comment

comments powered by Disqus
Okay... I've got a tough one for you All-Stars out there. I searched the board but couldn't come up with anything quite like my current obstacle...

IN SUMMARY: Has anyone figured out a way to do CONDITIONAL AVERAGING, with a dataset that could include NULL VALUES, based on MULTIPLE criteria similar to how one would use SUMPRODUCT?

What I'm shooting for is the AVG SCORE in the Summary Section to properly calculate the average score based on mutiple criteria (REP name and DATE RANGE) and the raw data in my raw data section (which can contain null values). In this case, John should evaluate to "7.0" and Jack should come out to be "3.0"

I found these two formulas on the board but they didn't seem to fit my needs:

What follows is a simplified example but you get the point:

******** ******************** ************************************************************************>Microsoft Excel - Ex of Conditional Averaging.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG8=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

~Hello. I'm adding a dashboard page to a complex tracking worksheet (macro-enabled, shared and protected Excel 2007). Where I'm stuck is trying to autopopulate a row showing a count of the number of dates in a ReviewDate column that fall between certain dates only if multiple criteria are met. I've attached a sample spreadsheet and mocked up someting below.

Sample excel file

The results will be on a dashboard page needs to have nicer (brand standard) formatting for senior leaders, which makes Pivot Tables difficult to use. The spreadsheet is not that large so calc times are not a concern (yet). I've tried COUNTIF, COUNTIFS, SUMPRODUCT and everything else I could think of and have struck out. I think my main problem is the stringing the complex syntax together properly.

Your expertise and suggestions are appreciated! If I can solve for #1 below, I can figure out the rest I think. Thank you!

Example of source data

ReviewDate Status Approach LOB 7/23/2011 Not Started ILT A Finance 7/25/2011 In Progress ILT B Finance 7/26/2011 Develop WBT A Tech 7/23/2011 Not Started WBT B Finance 7/25/2011 Complete WBT B Finance 7/26/2011 Complete ILT B Finance 7/29/2011 Develop ILT B HR 7/31/2011 In Progress ILT A Finance 8/2/2011 Not Started WBT A Finance

What I'm trying to figure out is:ILTs Due = COUNT of Approach "ILT A" OR "ILT B" where LOB = "Finance" AND Status = "In Progress" OR "Not Started" AND ReviewDate is between July 11-18, 18-24, 25-31, etc.ILTs Complete = ILT A or ILT B = "Complete" and LOB = "Finance" (just a simple running total)

Example of dashboard table
Week of July 11 July 18 July 25 August 1 August 8 ILTs Due 2 2 1 ILTs Complete 1 WBTs Due 1 WBTs Complete 1

Hi all,

I'm new to excel programming. I'm task to compute the average if several criteria is met. I have output.xls and inside this workook there is a button. When a user click this button, it will calculate for them the average. And I have another workbook, data.xls that has a total of 40 columns and 40640 rows(The data will get bigger). I have to find the average by month,week,service,testname and result. The result of this average will be in output.xls

Is there any macro to write so they know when to find the average? I'm using excel 2003. I don't even know where and how to start. So far I only tried the AVERAGE(IF(......
However,it is not efficient and sometimes it will come out an error.

Anybody can help?

Thanks in advance

I want to get a sum of a group of cells if multiple criteria is true.

Column A is the same "Date", and Column B is the same "Line", and column D is the same "Shift",

So if each row has the same Date, Line, and Shift, I want the sum of column "E" for those respective rows to go in column F for those respective rows.

Hope that makes sence, see attached for example, notice Shift changes...

Please note I need to do this in an Excel 2003 atmosphere.

Hi all,

I've been struggling with the COUNTIFS and AVERAGEIFS functions when dealing with the ABS function.

What I want to do is:

=COUNTIFS(Criteria_range1,criteria1,Criteria_range2,criteria2, etc...)

Which is working fine until I want to use the ABS function within this function, for example count if column A contains 1 and column D contains any number greater than 1. (same for average)

=COUNTIFS(A:A,1,D:D,">1") and =AVERAGEIFS(D:D,A:A,1,D:D,">1")

How can I amend this so I can change that second criteria to look at the absolute values stored in column D...

=COUNTIFS(A:A,1,ABS(D:D),">1") and =AVERAGEIFS(D:D,A:A,1,ABS(D:D),">1")

Doesn't work and I've been struggling with other ways of writing this so excel knows what I want it to do.

Can I use INDEX(ABS(D:D),0,1) somehow? I have used this to find a average, with other criteria..

to give me the average of the Absolute values in D that meet the criteria, could I somehow use this to solve my issue.

Appreciate the assistance.


I currently have this forumula:

=SUMPRODUCT(COUNTIF(A4, INDIRECT("'"&"Sheet5"&"'!A2:A35"))) + SUMPRODUCT(COUNTIF(INDIRECT("'"&"Sheet5"&"'!B2:B35"), "Active"))

I am trying to have a count of all the statements that contains the value in A4 which is "EBAY" and have a status of "Active" from a list of 1000 statements. Currently this counts all the statements of Ebay and then adds on top all the Active a total of 170. I in fact want the value 8 which is the open ebay statements.

So I need this to only count if both criteria are met. This is across multiple sheets hence the sumproduct.

Any Ideas?


I'm trying to find the sum of a range of values based on multiple criteria, and the criteria is that the fields all have to be identical, then sum them. I've attached a brief example spreadsheet that has the fields

What I want the formula to do is first find the range of all the matching states, then find the range of all the matching Cities within the states, and then sum the values based on them having matching city values.

I've been able to do that with one criteria using SumIf, I'm not sure if this will help paint an image of what I want to do:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But I can't figure out the way to do multiple criteria against itself. Most of the results I get from Google using multiple criteria are using a set few values, and I can't seem to figure out how to alter those methods to work with my situation.

Any help is much appreciated

Can you please look at the formula in Tab 2 ('Jan & Feb 2012 Survey Stats') in Cell H3 of the attached file and help me with the following:

Based on the Data in Tab 1 ('Data Input') I am trying to calculate, by pharmacy (column C of the 'Data Input' Tab), and by type (column I of the 'Data Input' Tab) the average survey score for all data (Columns K through AC of the 'Data Input' Tab if the score is above 0 (zero))

If using Columns K through AC of the 'Data Input' Tab is not feasible, we can use the already created averages in Column J, which is already providing averages and discounting scores of 0 (zero).

Thanks for the help, really appreciate it

I'm trying to look at multiple criteria on one sheet and return the value (date) from that row. I'm having the hardest time for some reason. Basically, explained below and I've attached a spreadsheet too.

I want to know if A2=Column A in sharepoint tab and $b$1=Column C in sharepoint tab, return the value from Sharepoint tab Column F to Cell B2. Need this for all columns and rows.

All help would be greatly appreciated!


I have read up on counting rows with multiple criteria. But I have not seen the following situation addressed, was wondering if you could help…

How many rows have "M" or "O" in column A and "X" in column B.

From the example attached/below the answer should be 2. But I can not get it to work.




I am trying to create graphs based on a list box and a table of data.

Problem is i have to look-up multiple criteria to get the final answer.

Please refer to the attached excel sheet.

In the 'Employee Breakdown' sheet, there are multiple employees with respective data. The data is similar to all employees and therefore so are the row names.

The first graph i started to create was to look-up hours available, project hours and proposal hours.

Problem is, how do i get the data?!

For instance, if i look-up employee 'J.Smith' from the list box, and then want to look up 'project hours' how is this done? I have playing with the INDEX and MATCH functions to no avail.

The problem gets more difficult if i want to get the data for employee 'A. Jones' as there is a similar row labelled 'project hours' etc.

I would like the range to include the entire data table with all employees included therefore the look-up function needs to isolate the employee selected from the list box and find the relevant 'project hour' and 'proposal hour' rows that relate to that employee only.

Obviously, when the name is changed in the list box, the data in the graph rows change accordingly.

'If' statements are not an option as there will be in excess of 30 employees.

Also i require an answer that does not use a pivot table.

Thanks so much in advance for assistance.

Hi There,

I'm fairly new to the more advanced functions of excel and I'm struggling to find a way of doing multiple criteria lookups and then concatenating multiple results into one cell and I'm hoping that one of Ladies and Gents on the forum may be able to help.

I am trying to create a spreadsheet to display a schedule of various computers that need restarting or updating regularly. I have attached a copy of the spreadsheet I am basing this on. The first worksheet has a schedule of 4 weeks with weekdays. The second worksheet is a table of each computer and the week number and days that it restarts.

I am trying to take the week and day in the Schedule sheet and then lookup from the Servers worksheet and return the values for every server that contains that week number and that day and enter them into the cell in a soft returned list.

So for example Week one Monday. Cell B3 would lookup B2 (Monday) and A9 (1) then it would look at the table in the next worksheet and would return Server1 and Server4 as they both have 1 in the Week and Monday in the Day.

I looked at using index but I struggled with the formula.

I would greatly appreciate any insight that anyone can give on this.

Many thanks in advance,


Hello, I am having difficulty being able to Count Occurences where multiple criteria are met across multiple columns.

For example, in column C, I have "True" and "False" Values, and in column H I have "True" and "False" Values. I would like to be able to count the number of occurences where "True" is found in the same row, in both columns.

THe formula I had been using in the past does not seem to be working:

=COUNT((IF('Raw Data'!C$4:C$504="TRUE",IF('Raw Data'!H$4:H$504="True","",'Raw Data'!H$4:H$504))))



Hi, I am working on a spreadsheet that I need to filter some records based on multiple criteria and copy the resulting records to another worksheet starting from last empty row. Any help of coding this would be appreciated.

Here is what I am trying to do:

Sheet1: I have the following data

ID | Name | Pass/Fail (Yes/No) | Grade

Sheet2: I have the following headings

ID | Reason | Date
78.............Not taken the exam.......2/12/2011
95.............Postpone the exam........4/12/2011

I wanted to filter sheet1 based on “Yes” from Pass/Fail field and N/A in grade
field (Kelly and Rey would result..) and get just the id numbers 49 and 56 add it
to sheet2. But in the copy process I wanted to add reason as “Not taken exam”
and date as today’s date.

After the copy process Sheet2: should look like as follows:

ID | Reason | Date
78.............Not taken the exam........2/12/2011
95.............Postpone the exam.........4/12/2011
49............Not taken the exam........01/4/2012
56.............Not taken the exam........01/4/2012

I am working on following code but I get errors. Is there any other way to do this? Thanks a bunch!!

Dim rngDest As Range
Set rngDest = worksheets(“Sheet2”).Range(“A” & CStr(Application.Row.Count)).End(xlUp).Offset(1,0)

With ActiveSheet

If WorksheetFunction.CountIFS(.columns(3), “Yes”, .Columns(4), “N/A”) <> ) then
.AutoFilterMode = False
.Range (“A15:A250”).AutoFilter Field:=3, Criteria1:”Yes”, Operator:=xlAnd
.Range (“A15:A250”).AutoFilter Field:=4, Criteria1:”N/A”
ActiveSheet.UsedRange.Copy Destination:rngDest
.AutofilterMode = False
.Application.CutCopyMode = False
End If
End With


I'm new to this forum and hope someone can guide me in the right direction.

I've been stuck on this project for days now, have looked into various forums including ozgrid but still haven't found a solution. I'm thinking about this even while sleeping

1. I have 2 sheets "Criteria" and "Actual Data". The Autofilter Criteria is defined on the "Criteria"
2. The idea is to run macro through the criteria defined on the "Criteria" tab and perform action on the "Actual Data".
3. The macro for autofilter is executed on "Actual Data" sheet. I've managed to assign the autofilter criteria to the cells reference in "Criteria" tab but what I'm struggling with is how to loop through the autofilter to the next row or until all criterias have been met.

With the loop I have 2 action that needs to be performed

1. Copy values subsequent cell value on "Criteria" tab on column L to the filtered data on "Actual Data" tab (again this is a loop that I' unable to execute)
2. Criteria tab - If YES is defined from column M to P, clear contents of the data on "Actual Data" of columns C to F.

I'm attaching my example so its easy to understand where I'm stuck.

Thanks in anticipation.

Sub Autofilter() 
    Application.ScreenUpdating = False 
    ActiveSheet.AutoFilterMode = False 
     ' Filter using the cell reference on the "Criteria" worksheet
    Range("4:4").Autofilter Field:=18, Criteria1:=Range("Criteria!A6").Text 
    Range("4:4").Autofilter Field:=19, Criteria1:=Range("Criteria!B6").Text 
    Range("4:4").Autofilter Field:=15, Criteria1:=Range("Criteria!C6").Text 
    Range("4:4").Autofilter Field:=11, Criteria1:=Range("Criteria!D6").Text 
    Range("4:4").Autofilter Field:=14, Criteria1:=Range("Criteria!E6").Text 
    Range("4:4").Autofilter Field:=20, Criteria1:=Range("Criteria!F6").Text 
    Range("4:4").Autofilter Field:=21, Criteria1:=Range("Criteria!G6").Text 
    Range("4:4").Autofilter Field:=22, Criteria1:=Range("Criteria!H6").Text 
    Range("4:4").Autofilter Field:=23, Criteria1:=Range("Criteria!I6").Text 
    Range("4:4").Autofilter Field:=3, Criteria1:=Range("Criteria!J6").Text 
    Range("4:4").Autofilter Field:=5, Criteria1:=Range("Criteria!K6").Text 
     ' Copy column "L" values on "Criteria" worksheet and paste on the "Actual Data" worksheet on the filtered data for each
    Sheets("Actual Data").Select 
    ActiveCell.Offset(1, 0).Select 
    Range(Selection, Selection.End(xlDown)).Select 
     ' Clear contents of the column on "Actual Data" worksheet if "YES" is defined - If "NO" no action required
    ActiveCell.Offset(1, 0).Select 
    Range(Selection, Selection.End(xlDown)).Select 
    ActiveCell.Offset(1, 0).Select 
    Range(Selection, Selection.End(xlDown)).Select 
    ActiveSheet.AutoFilterMode = False 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi there, I'm not sure how useful the title is but hopefully I can explain it a little more clearly -

I'm formulating a stock picking model for college; there are 100 assets and I give each asset a weighting based on multiple criteria across 50 periods (for historical testing).

I want the model to allocate 10% of the fund to each of the top 5 (50% total) and 5% to the next 10 (50% total) so it invests in the top 15 assets. This alloaction is for each period.

So far so good - I can get it to do all that.

However, each asset is classified into one of ten sectors or types and I don't want the model to invest more than 25% in each sector. I'm clueless on how to do this. Can anyone help?

Does that make sense?

My data is sorted like this, with the weightings in the table:

Asset1 Asset2 Asset3 ... Asset100
Sector 1 1 4
Period 1
Period 2
Period 3
Period 50

Thanks all!

Hello all,

I would like a lookup that takes multiple criteria and that is not an array formula! Unfortunately I decided to use array formulae and my spreadsheet went to over 45mb!! Not good.

I've searched the forum for an answer to my questions but couldn't find any!
I've attached a spreadsheet as an example. The examples I am using have {Sum(IF)} formulae in it (array) and I would like to change those to others that will not increase the file size so much and will not take too long to calculate.

Basically, I would like a lookup that will return me the Amount Paid and Full Cost based on the person's name and the date.

the data and the results table are both on separate sheets.

It would be nice to bring that file's size back down to less than 4mb!!



Good day all,

I've been racking my brains today but can't quite get my head around this.

I want to ultimately highlight a row on one sheet if multiple criteria are met on the same row on another sheet.

I've attached the spreadsheet in question.

Sheet 1 has the data that i want to highlight and sheet 2 is what i need to check against.


The primary criteria are the 'Store' (Column A) - 'Product Code' (Column D) - Date (Column I)

Any help would be appreciated. Thanks.

What's the best way to return a specified cell value from a separate
tab/sheet, using multiple criteria, and without sorting the source data/table
(see below example)?

Tab 1 / Sheet 1 - Input Data
B2:B10 Division Values - Division 1, Division 2, or Division 3
C2:C10 Category Values - Revenue, Expenses, Profit
D2:O10 Amounts - Monthly amounts for each B2:B10 Divisions and B2:B10

Tabs 2, 3 and 4 - Extracted Lookup and Display Data
In 3 separate sheets/tabs dedicated to each Division, I want to display, in
Cells C1 through N3, the monthly Amounts (i.e. the appropriate row of monthly
amounts) from Tab 1 based on the the Divisions entered in Cell A1 of Tabs 2-4
(e.g. Tab1, Cell A1 = Division 1), and the Categories entered in Cells B1:B3.



I'm trying to setup a function that will check to see if multiple criteria is
met given three different cells. To be more clear.

I have three columns A, B, C and three different criteria.
If a cell in column A=100, and Column B>=95, and Column C>=90 then the
response is Platinum.
If the cell in Column A=100, and Column B>=92 but below 95, and Column C=>85
but below 90 then the response is Gold.

Can someone provide some help with this one?



I have a worksheet with a summary page like
Column A Customer name
Column B Industry and in colum H I want to return a budget figure based on
the two criteria of Column A & B. The budget figure is in another sheet
where column A & B once again have the customer name and Industry and the
budget figure is in column C. How do I get the figure from the second sheet
in column C using the multiple criteria into the first sheet? hope this
makes sense.


Hi All,

I have a spreadsheet, attached, with basedata in sheet 1. On sheet 2, I need to be able to look up values from different columns from the basedata in sheet 1 (I have not named the ranges as more rows of data will be added each week) and count the results. There are two types of result - P or D.

I have tried this simply using counta, even sumproduct but the real issue is the look up of the multiple criteria in columns plus the criteria in the row.

I have explained in the attachment with an example of the outcome in the yellow cells so it should be self explanetory but feel free to ask

Payment through paypal, once done as needed,

Hoping someone can help me

Thanks Rossey


I am looking for a way to find the Top 10 Largest (Col K - Est Billings) records from a table in a separate worksheet and automatically populate another table in another worksheet and then Lookup (INDEX/MATCH??) some associated column values from the record. However, there is multiple criteria based on column values in the main data table (Data worksheet) that must be met in order to be a "Top 10". I attached my file (Top 10 Lookup.xlsx) for reference.

The main table on the Data sheet is sales opportunity records with different active stages and outcomes (win, loss, etc.) for the current year. This populated on a weekly basis. The Top 10 table will list the top 10 largest NEW "Open" Status records that have been added (based on Open Date) to the dataset relative to a specific Week Beginning Date and End Date (these will manually inputted into the report). A NEW could also be a record that previously was a Stage 1 and then was moved to Stages 2-5 during the previous week. Below is more info on the definition and criteria for a "Top New Opportunity" record. Hope this helps and thank you in advance for your help.

Definition of NEW = Open Date falls BETWEEN Week Beginning and End Dates AND between Stages 2 - 5 OR Previous Stage = "Stage 1" AND Stage "Stage 1" AND Sales Stage Date falls BETWEEN Week Beginning and End Dates
Criteria for Top New Opportunities: Col E (Status) = "Open" Col L (Prev Stage) = "Stage 1" Col G (Stage) "Stage 1" Col M (Sales Stage Date) >= D3 (3/11/12) Col M (Sales Stage Date) = D3 (3/11/12) Col B (Open Date)

I'm trying to setup a function that will check to see if multiple criteria is
met given three different cells. To be more clear.

I have three columns A, B, C and three different criteria.
If a cell in column A=100, and Column B>=95, and Column C>=90 then the
response is Platinum.
If the cell in Column A=100, and Column B>=92 but below 95, and Column C=>85
but below 90 then the response is Gold.

Can someone provide some help with this one?


No luck finding an answer? You could always try Google.