# Average Based On Multiple Conditions

Dec 1, 2009

Im currently trying to report on cycle times for a certain task that is carried out on a daily basis. I have used NETWORKDAYS to calc each rows cycle time in Cell R14:R512 the code is:-

=IF((NETWORKDAYS(O14,N14)-1)*(\$AH\$11-\$AH\$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),\$AH\$11,\$AH\$10),\$AH\$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),\$AH\$11,\$AH\$10)=0,"",(NETWORKDAYS(O14,N14)-1)*(\$AH\$11-\$AH\$10)+IF(NETWORKDAYS(N14,N14),MEDIAN(MOD(N14,1),\$AH\$11,\$AH\$10),\$AH\$11)-MEDIAN(NETWORKDAYS(O14,O14)*MOD(O14,1),\$AH\$11,\$AH\$10))

This is returning the cycle time providing both the start date/time and end date/time are filled in, or it returns blank if nothing has been filled in. I have then tried to calc the average overall cycle time in Cell R12 by using the below formula = SUM(R14:R513) / (COUNT(R14:R513) - COUNTIF(R14:R513,0))

This seems to be working proved i have two rows of data filled in. My problem lies when all the cells are blank, it then returns the DIV error. I have tried using an if statement to ignore if the cell is blank but it still returns the DIV error. Im really at a loss on this and now im not even sure if i have gone about this in the right way or if there is a better way to approach the whole task? I have attached a sample workbook to try and better explain.

## Calculating The Average Of A Column Based On Multiple Conditions

Feb 21, 2008

How do I calculate the average based on multiple conditions? Is my syntax incorrect?

Here's what i'm looking for:

=average((IF(X1:X1000=A1)*(Y1:Y1000=B1),AA1:AA1000))

## Average Based On Conditions?

Jul 1, 2014

I have attached a file where there is information in the sheet Weekly with the first row showing the same month for several weeks. add a formula in the sheet Monthly which would calculate average for each month based on the first row in Weekly sheet. Plus the grouping in the Weekly sheet is done by shops and in the Monthly sheet by food, which would I imagine make the formula more complex.

## Sumproduct - Average With Multiple Conditions?

May 7, 2012

I need to average with multiple conditions. Is there a way for sumproduct to do such a thing? How to average with conditions?

## Average With Multiple Conditions, Including Dates

Aug 20, 2008

My spreadsheet is too large to attach so I'll describe it as best I can. I have names in column H, which are repeated irregularly. I have corresponding grade numbers (1-5) in column I, and I have the dates they were entered in column J. It looks something like this:

Column H Column I Column J
Name 1 4 8/10/08
Name 2 3.5 8/11/08
Name 1 5 8/11/08
Name 5 4 8/12/08
Etc....

I want to average the grades (column I) for a certain name (column H) within the last 30 days, or however many days I enter. I'm using Excel 2003 with the analysis add-in. I can use SUMIF to include any two columns, but how can I write an average grade formula using both the name and date conditions? Dan Auto Merged Post Until 24 Hrs Passes;Come to think of it, all I need to do is sum the data. I have a denominator in a different box that I can use to average it. So in effect, I'm trying to creat a SUMIFS function.

## Sum Based On Multiple Conditions

Aug 28, 2009

I am trying to sum numbers based on three counditions.

My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd*

The yellow cell contains the formula I am using.

## VBA To Set Cell Value Based On Multiple Conditions

Jan 4, 2013

I am creating a document log that tracks all excel files sent and received.

I use RDBmerge to get the filenames and data from the file batches.

My current macro edits the data down to the last stage of data needed to create the log.

My example workbook shows the final stage of the RDBMerge Data "Rough_Data" and then the final data formatted needed for the Log "Final_Data"

Included on the "Rough_Data" worksheet is the Code log used to complete the "Final_Data" worksheet.

I have highlight the cell range on the "Final_Data" Sheet that I need to complete.

filenames can occur multiple times from the RDBmerge, so only one occurance of the filename is place in column B of the "Final_Data" worksheet.

The criteria is this:

A column on the "Final_Data" worksheet as been Named for Each of the possible "Record Types" from Column G of the "Rough_Data" Worksheet. ("A,B,C,D,E,I,O,P,Q,T,V,W,X,Y")

I then must look through the "Rough_Data" worksheet for the first occurrence (if any) of that record type for each filename on the "Final_Data worksheet. If an occurrence is found I then must look at the "Unique ID" & "Program" Columns of the "Rough_Data" Worksheet. The key (on the "Rough_Data" worksheet) is used to determine the code that is written to the corresponding cell.

i.e.
"Final_Data"Date
FileName
Field2
User
Direction
Method

[Code] .....

I would very much like to automate this process, as sometimes I am dealing with over a hundred files with 30 plus rows of data each.

## Summing Based On Multiple Conditions

Feb 11, 2010

I have been trying to search an array and cannot find the correct combination of functions to get the information I want. I might need a macro to do this but I will try anything. Here is an example:

1 A 1
1 B 2
2 A 3
2 B 4
3 A 5
3 B 6

I want to search for 3 and A and return the value in the 3rd column. I have tried several functions but cant figure out how to make sure I get the correct value in the 2nd column that corresponds to the value in the 1st column.

## Cell Value Based On Multiple Conditions

Feb 23, 2010

I need to create a macro that will calculate a value based on the contents of multiple cells. Looking at the example attached, if columns A and/or B (employee ID and name) are empty, then allowable OT (G) should be 0. If either have data, then if Stage (F) is CAN, G should be 20. If Stage is FAS or FAR, G should be 10.

I'm not very savvy with IF statements, which is how I imagine this can be done, and I don't know if this would be easier to do as a macro or as a formula within G. Since the contents of the of the cells will be changing on a weekly basis, I'd prefer the formula to only be there if there is content on the line, so we don't have nulls showing.

## Flagging Duplicates Based On Multiple Conditions

Nov 8, 2008

Flagging Duplicates based on Multiple conditions..

I have attached the file for reference with Dummy Data as the actual data runs in thousands...

What I require is to flag Duplicates with some formula so that I can filter and then later delete those specific rows from the records...

The Duplications has to be considered on Multiple criterions:
1. If the FNAme and LName is appearing for one Company more than once.

2. Sometimes there would be inconsistency in Data Entry, so the FNAme would be typed in the Column for LName and vice-versa.The code needs to understand the same.

3. Alongwith the above, if the Designation is different then the record with the Lower Designation based on the Weightage should be Flagged as "Duplicate".
Ex: -

4. If there's a tie between the same designation then the record with the Maximum information across all the columns having Max info should be Unique.
Ex:- The amount of details mentioned in the following columns would determine the record to be termed as Duplicate or unique

5. If still the records are identical then the first occurence can be unique...

I have made this data to give an example as the actual data is very confidential.

Please allow room for adding New Designations as the Designation and their Weightage needs to be Dynamic.

Please refere the Word doc Duplication Criterion Explanation for the explanation.

## Lookup Unique Based On Multiple Conditions ...

Jan 2, 2009

I am looking for a solution other than using an advanced data filter for unique records only.

I would like to take a large list (columns A:D), and automatically filter for unique records and other conditions and paste the results in different tables (Group A & Group B).

Group A only includes records with value ="A" in the checksheet column.
Group B includes records with value <>"A" in the checksheet column.

Does anyone have any ideas how to do this so that when I paste the large list in columns A:D, the other tables (Group A & Group B) are automatically populated?

SEE ATTACHED:

## Counts Info Based On Multiple Conditions

Feb 22, 2009

I am trying to get a list which counts info based on multiple conditions but I can find no way round this one point

I have a sheet which has all the games played in a particular division and the time that each goal was scored and I am trying to break down the following

What I want is a list that has a column that has the result of how many times when team A were playing at home was a goal scored between the 45th and 65th minute

column A = Home Team
B=Away Team
C= Time of First goal
D=Time of Second goal
E=Time of Third etc

I have a countifs in place for counting the home team and counting if the first goal was scored in under 10 minutes (this is simple as obviously all the first goals are in column C) but what I want to do is be able to have a count of how many times a goal was scored between x number of minutes eg between 40-50.

My problem comes when trying to count the goal times as some matches will have no goals and the cells will be blank and others will have multiple cells filled and the figure I want could be in any column and I also only want it to count only the amount of games a goal was scored between those times so if a game had 3 goals scored in between that time I only want it to count 1.

The sheet has a lot more columns but this is the main sticking point.

## Finding Information Based Upon Multiple Conditions

Mar 23, 2009

I want to populate separate tabs from data in a master table based upon multiple conditions shown in the separate tabs. Each tab is a summary of a particular engineer's shifts over multiple weeks and their results on various projects. Each summary tab has the engineer's name and the number of the week (the two conditions). Example: On Aaron's tab, I want to show a summary of Aaron's work and his results over a period of several months, as well as who is working the other shift on that day for those projects. So I need to get the following information for Aaron's tab from the master table:

Week 1, Aaron is working the morning shift and Zeb is working the afternoon shift,
Week 2, Bill is working the morning shift and Aaron is working the afternoon shift
Week 3, Aaron is working the morning shift and John is working the afternoon shift
etc etc

I also need to know how much work each engineer pairing has achieved in a particular week on a particular project. The engineer working in each shift may vary from week to week, as will their results. There is no formula for when a particular engineer will be working either morning or afternoon or whom they will be working with.

## Match Function Based On Multiple Conditions In VBA

Jun 7, 2013

I am trying to write a code in excel vba where I need to match specific text in multiple columns and if text is matched then I need to insert new blank column after the specific text.

My code is like this.

Dim Col_num As Integer
Col_num = Application.WorksheetFunction.Match("Customer Defined 06", Range("6:6"), 0) + 1
Columns(Col_num).Select
Selection.EntireColumn.Insert
Cells(6, Col_num).Select

This code is working fine in single condition but when I enter another condition then its giving me an error i.e Type Mismatch.

## Delete Cells Based On Multiple Conditions?

Jul 16, 2013

I'm trying to remove cells that contain certain suffixs. Here is the code I'm trying to use:

Code:
For i = 7 To 2000
If Cells(i, 7) = "IV," Or Cells(i, 7) = "Jr," Or Cells(i, 7) = "III,"
Then Cells(i, 7).DELETE Shift:=xlToLeftEnd IfNext i

but it does absolutely nothing. I've used this for similar macros in the past and it worked just fine.

## Lookup Value From List Based On Multiple Conditions

Dec 21, 2007

I have a list of holiday data which has the following info. Name, Date from, date to, hours off per day. I need to look up this information and place the number of hours against each day for each person. The problem I am having is that I can't get the hours into the cells for which the date is between the date from and date to. I have attached spreadsheet and how I want the summary page to look. I just need the correct formula inserting.

## Create New List Based On Multiple Conditions

Mar 6, 2008

I am trying to filter a range of text and copy only those entries from it that meet selected criteria into a new part of my page. The entries will all look like the following but will have varying numbers before the final Equity/Index part:

IBM US 3 C60 Equity
IBM US 3 P60 Equity
UKX 3 P5000 Index
UKX 3 C5000 Index

I have constructed something using a modification of the find nth word function from this site that allows me to filter based on whether the fourth part (or for the third and fourth choices, third part) shows C or P at the beginning and then based on the value that is written after the letter. However, I have to break down the code a bit further to firstly check whether the final word of the string is Index or Equity to decide which word to check for C/P. I was wondering if anyone knew whether there is possibly a more simple way of running a find within one cell than using the nth word function. Would it possibly be a case of making two subs within a different module and then calling them to look for the specific word number based on what I have in F6 (my original equity/index ticker symbol: for example IBM US Equity or UKX Index)? Attaching code below. I'm sure it doesn't make a lot of difference but in case it helps I am currently running Excel 2007 on a Vista machine but I also use it at work on a Win XP PC with 2003.

----CODE----........................

## Sum Based On Multiple Conditions & Variable Range

May 30, 2008

I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached.
This has been driving me nuts for a few weeks now.

## Identifying Rows To Delete Based On Multiple Conditions?

Jun 5, 2014

Maybe this has been taken care of in another thread, but still:

ROW ID NUM OCC K OCC I
1 222222 0 0
2 222222 0 0
3
4 333333 0 0
5 333333 0 0
6 333333 0 0
7
8 444455 1 1
9 444455 1 1

I have a situation like above, but what I want to do is:

=FOR(ROW1:ROW9, IF COUNT ROWS(ID_NUM)>= 2,

(create name for set?)

AND

IF OCC_K AND OR OCC_I VALUES >= 0,

THEN DELETE ROWS(within set) >=2

Or more simply:

If there are rows with multiple Accident ID numbers, name that set,
and if OCC_K and/or OCC_K values are BOTH >= 0,
then delete all duplicate rows?

Yes I figure there needs to be some way to distinguish Row 1 from Row 2, (flagging one of the two?) so that ROW 2 can be identified, and similarly with Row 4 from Row 5 from Row 6, but the general flow of commands is what I'm trying to get at.

Is it also possible to use AND and OR in the same line? Or BOTH?

(I have other variations on this that I also like to ask about, such as multiple rows within a set that meet certain conditions that are identified in a different field, but for now this is a simpler version.)

I would like to think I'm not going to need a macro, and that I can do this in a Formula.

## Return Data From Table Based On Multiple Conditions

Jul 26, 2014

Want to be able to select data from Column C (3 data validations/drop down menus) and have the corresponding values display in the appropriate fields in column H. Not sure if this is possible?

## Record Duplicate Values Based On Multiple Conditions Only

May 1, 2014

I am trying to pick out certain bits of information from the below "example" set of data:

A
1

A
1

B
1

C
1

[Code] .......

My aim is to record the letters that are recorded against both numbers (note: in my data there are more than 2 sets of numbers). For the example above the solution would be:

A
1,2

B
1,2

...because these two letters appear against both 1 and 2.

There are some letters that are duplicated against the same number which is making it hard for me to work out. I don't care if the same letter appears against the same number, I just would like to know instances when a letter appears with a different number, and if possible what that number is.

## Generate Numbers From Another Sheet Based Multiple Conditions

Nov 21, 2007

I am trying to generate a number based on the Month, Year and then three digits. For Example: 1107-001. I would need it to refer to another sheet for the previous used last three digits. Each month it would start over at 001. On the Reference sheet it would always be in the first column.

## Count Number Of Unique Values Based On Multiple Conditions

Feb 22, 2011

I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.

In excel is best illustrated what I mean!

In total, 5267 lines and need to do this with all. You can do this in excel?

## Count Unique Occurrences Based On Multiple Conditions In Another Cells

Sep 19, 2013

I am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.

I've attached an example sheet : Example email report.xlsx

The result I need is: The number of unique values in column E, that also have NULL in column G and NULL in column I.

So, in the example, the result would be 7.

I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.

## Multiple Conditional Format Conditions Changed Based On Another Cells Value?

Jun 12, 2009

A1:A6 have numeric values 1 to 6.

I want the conditional format values to change Based on the value in Cell B2.

If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text.

IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text.

I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.

## Fill Cells With Some Specific Texts Based On Multiple Conditions

Jun 18, 2014

I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.

Code to fill these blank cells automatically with some specific texts based on the attached conditions.

I have attached the workbook for more details. FillCellsTest.xlsm‎

## Change Cell Background Color Based On Multiple Conditions

Aug 6, 2008

My Excel skills are basic at best, with no knowledge of VBA other than finding the VB Editor. What I'm trying to do: Attached is the file I'm working with, to give you a better idea. When a name is selected in column B, the cells in the non corresponding columns E to N should have a black background color. For example, when you select Mike in column B, the cells in that row in columns I to N, named Dave and Dean, should become black. If you select the name Dave, columns E to H (Mike) and M to N (Dean) should become black. In addition, the background color of the non-black cells should change depending on the status.

OK : green
FAIL: red
N/A: orange
Exceptions: blue

The attached file shows a few possible results, manually entered.

## Average Of Numbers From List Based On Multiple IF-OR Criteria

Feb 11, 2014

Here is my formula that does not return accurate results:

=IFERROR(SUM(SUMIFS(Tenure!\$H:\$H,Tenure!\$E:\$E,{"=1100","=1090"},Tenure!\$C:\$C,{"=12","=14","=13"},Tenure!\$G:\$G,"=1/1/2013",""})/SUM(COUNTIFS(Tenure!\$E:\$E,{"=1100","=1090"},Tenure!\$C:\$C,{"=12","=14","=13"},Tenure!\$G:\$G,"=1/1/2013",""}))),0)

I need to average column H from the sheet Tenure IF column E is 1100 or 1090 (formatted as text) and IF column C is equal to 12, 13, or 14 and IF column G is less than or equal to a date and column I if greater than or equal to a date OR if this column is blank.

I tried AVERAGEIFS, the above route and many others. For whatever reason, I have been unsuccessful, but close many times.

## Average For 3 Conditions

May 12, 2008

I need your expert advise on how i can get the results i need. What i'm trying to do is get the average number of days given that 3 conditions are met.

2. Status
3. Account

So here are the things i did

1. Named Ranges
ACCT for the Account
MR for the Month
STATUS for the status
TATQ for the no of days

2. I created a validation list in specific cells for the condition

a5 to a12 are the list of accounts
b2 for the month
d2 for the status

3. used the formula...........

## Finding Average With Few Conditions

Nov 20, 2009

I am using EXCEL 2003. I am to find Average using few conditions. I have explained the requirement in the sample workbook.