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.

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

--
Jacob


"AAA1986" wrote:

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.

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.

--
Biff
Microsoft Excel MVP


"AAA1986" wrote in message
...
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.

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" wrote:

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

--
Jacob


"AAA1986" wrote:

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.

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

--
Jacob


"AAA1986" wrote:

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" wrote:

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

--
Jacob


"AAA1986" wrote:

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.