SUMIF/COUNTIF with missing data

  • Thread starter Thread starter AAA1986
  • Start date Start date
A

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.
 
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)))
 
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.
 
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?
 
Back
Top