Sumproduct with multiple criteria and both numbers and strings

[Babymech]

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

 

[Jacob Skaria]

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

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF(ErrorCheck="Yes",IF(Group="Orange",Rating)))

** 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")

 

[T. Valko]

=AVERAGE(IF((A1:A20="Yes,")*(B1:B20="Orange")*

(ISNUMBER(C1:C20)),C1:C20))

=AVERAGE(IF((A1:A20="Yes")*(B1:B20="Orange"),C1:C20))