SUMIF/COUNTIF with missing data

[AAA1986]

Right now, I'm using the following function to get the average of column B,
given that column A has a certain criteria:


=SUMIF($A$1:$A$10,"=Blue",$B$1:$B$10)/COUNTIF($A$1:$A$10,"=Blue")

Which works fine, except that for some of the entries whose A column says
"blue" there is no data in the B column, so the denominator is off. How can I
account for missing data in this function?

Thanks in advance.

 

[Jacob Skaria]

Try

=SUMIF($A$1:$A$10,"Blue",$B$1:$B$10)/SUMPRODUCT(($A$1:$A$10="Blue")
*(B1:B10<>""))

OR try the below 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:A10="Blue",IF(B1:B10<>"",B1:B10)))

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF(A1:A10="blue",IF(B1:B10<>"",B1:B10)))

** 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.

 

[AAA1986]

Thanks! The first one works great. But what's the difference between the
first one and the array one? Is there any reason I should use one instead of
the other?

 

[Jacob Skaria]

The 1st one is a bit lenghty; but I would prefer the 1st one..