Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with multiple criteria and both numbers and strings
I thought I understood the basic principles of SUMPRODUCT but when I add too
many criteria I completely lose track of the syntax - any help is appreciated. Basically I have a number of named ranges that I want to check for multiple criteria, and, at the end of the day, average. The ranges are as follows: ErrorCheck (contains a number of different strings) Group (contains a number of different strings) Rating (contains a number, OR the word "Pass" OR the word "Fail") How do I find the average of the numbers in Rating for which ErrorCheck = "Yes," and Group = "Orange", and ignore the entries in Rating which are strings? Thanks, Baybmech |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with multiple criteria and both numbers and strings
Alternative..Try the below array formula..Please note that this is an array
formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF((A1:A20="Yes,")*(B1:B20="Orange")* (ISNUMBER(C1:C20)),C1:C20)) If this post helps click Yes --------------- Jacob Skaria "Babymech" wrote: I thought I understood the basic principles of SUMPRODUCT but when I add too many criteria I completely lose track of the syntax - any help is appreciated. Basically I have a number of named ranges that I want to check for multiple criteria, and, at the end of the day, average. The ranges are as follows: ErrorCheck (contains a number of different strings) Group (contains a number of different strings) Rating (contains a number, OR the word "Pass" OR the word "Fail") How do I find the average of the numbers in Rating for which ErrorCheck = "Yes," and Group = "Orange", and ignore the entries in Rating which are strings? Thanks, Baybmech |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with multiple criteria and both numbers and strings
Try this array formula** :
=AVERAGE(IF(ErrorCheck="Yes",IF(Group="Orange",Rat ing))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. If you're using Excel 2007: =AVERAGEIFS(Rating,ErrorCheck,"Yes",Group,"Orange" ) -- Biff Microsoft Excel MVP "Babymech" wrote in message ... I thought I understood the basic principles of SUMPRODUCT but when I add too many criteria I completely lose track of the syntax - any help is appreciated. Basically I have a number of named ranges that I want to check for multiple criteria, and, at the end of the day, average. The ranges are as follows: ErrorCheck (contains a number of different strings) Group (contains a number of different strings) Rating (contains a number, OR the word "Pass" OR the word "Fail") How do I find the average of the numbers in Rating for which ErrorCheck = "Yes," and Group = "Orange", and ignore the entries in Rating which are strings? Thanks, Baybmech |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct with multiple criteria and both numbers and strings
=AVERAGE(IF((A1:A20="Yes,")*(B1:B20="Orange")*
(ISNUMBER(C1:C20)),C1:C20)) =AVERAGE(IF((A1:A20="Yes")*(B1:B20="Orange"),C1:C2 0)) -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Alternative..Try the below array formula..Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF((A1:A20="Yes,")*(B1:B20="Orange")* (ISNUMBER(C1:C20)),C1:C20)) If this post helps click Yes --------------- Jacob Skaria "Babymech" wrote: I thought I understood the basic principles of SUMPRODUCT but when I add too many criteria I completely lose track of the syntax - any help is appreciated. Basically I have a number of named ranges that I want to check for multiple criteria, and, at the end of the day, average. The ranges are as follows: ErrorCheck (contains a number of different strings) Group (contains a number of different strings) Rating (contains a number, OR the word "Pass" OR the word "Fail") How do I find the average of the numbers in Rating for which ErrorCheck = "Yes," and Group = "Orange", and ignore the entries in Rating which are strings? Thanks, Baybmech |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count using multiple criteria including text strings | Excel Worksheet Functions | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
Sumproduct multiple criteria | Excel Worksheet Functions | |||
SUMPRODUCT USING WORDS AS WELL AS NUMBERS FOR CRITERIA | Excel Worksheet Functions | |||
Using Sumproduct with multiple Criteria | Excel Worksheet Functions |