SUMIF/COUNTIF with missing data
 

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.

SUMIF/COUNTIF with missing data
 

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.

SUMIF/COUNTIF with missing data
 

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.

SUMIF/COUNTIF with missing data
 

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.

SUMIF/COUNTIF with missing data
 

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.