Home |
Search |
Today's Posts |
#1
|
|||
|
|||
nesting sum if and
Good Morning
I would like some help on a nesting function. I have 3 columns of data on a spreadsheet. The first column identifies a station. The second station list an error code, entered in text, such as the words "mechanical failures" or "adjustments". The 3rd column list downtime the station had, in a numerical value, such as 10 20 or 30 minutes for the specific entry Station Error Code Downtime 20.3 Adjustments 10 20.3 Mechanical Failures 10 40 Pneumatic Failure 5 40 PM's / Cleaning 5 40.5 Lack of components 10 50 Specialists TEF/MFE 30 50 Electrical Failures 25 How would I go about writing a function where, say for example, I could get a sum for the enries of dowtime for all the "Mechanical Failures" for Station "20.3" , basically setting 2 conditions which have to be met in order to add up the downtime. Thanks. |
#2
|
|||
|
|||
You probably will be a lot happier if you use a Pivot Table. See
http://peltiertech.com/Excel/Pivots/pivotstart.htm But for a formula solution: =SUMPRODUCT(--(A1:A100=D1),--(B1:B100=D2),C1:C100) where D1 contains the Station number and D2 contains the error code See http://www.mcgimpsey.com/excel/doubleneg.html for an explanation of the "--". In article , "BMSpell" wrote: I would like some help on a nesting function. I have 3 columns of data on a spreadsheet. The first column identifies a station. The second station list an error code, entered in text, such as the words "mechanical failures" or "adjustments". The 3rd column list downtime the station had, in a numerical value, such as 10 20 or 30 minutes for the specific entry Station Error Code Downtime 20.3 Adjustments 10 20.3 Mechanical Failures 10 40 Pneumatic Failure 5 40 PM's / Cleaning 5 40.5 Lack of components 10 50 Specialists TEF/MFE 30 50 Electrical Failures 25 How would I go about writing a function where, say for example, I could get a sum for the enries of dowtime for all the "Mechanical Failures" for Station "20.3" , basically setting 2 conditions which have to be met in order to add up the downtime. Thanks. |
#3
|
|||
|
|||
=SUMPRODUCT((A2:A8=20.3)*(ISNUMBER(SEARCH
("*mech*fail*",B2:B8)))*C2:C8) Be careful on data entry. User(s) can make keying errors. I tried to mitigate that by using "*mech*fail*". To ensure the formula captures all entries, I would build drop-down lists that the user(s) must choose from. HTH Jason Atlanta, GA -----Original Message----- Good Morning I would like some help on a nesting function. I have 3 columns of data on a spreadsheet. The first column identifies a station. The second station list an error code, entered in text, such as the words "mechanical failures" or "adjustments". The 3rd column list downtime the station had, in a numerical value, such as 10 20 or 30 minutes for the specific entry Station Error Code Downtime 20.3 Adjustments 10 20.3 Mechanical Failures 10 40 Pneumatic Failure 5 40 PM's / Cleaning 5 40.5 Lack of components 10 50 Specialists TEF/MFE 30 50 Electrical Failures 25 How would I go about writing a function where, say for example, I could get a sum for the enries of dowtime for all the "Mechanical Failures" for Station "20.3" , basically setting 2 conditions which have to be met in order to add up the downtime. Thanks. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nesting formulae with COUNTIF | Excel Discussion (Misc queries) | |||
nesting 18 x functions | Excel Worksheet Functions | |||
nesting if > 7 or using hlookups in a vlookup | Excel Worksheet Functions | |||
Nesting A Function | Excel Worksheet Functions | |||
Nesting If Statement with "AND", "OR" conditions | Excel Worksheet Functions |