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

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

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

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