Average IF

  • Thread starter Thread starter carl
  • Start date Start date
C

carl

Trying to write an IF formula that will look down columnA
and average the numbers if cellB1=D1 and cellC1=E1.

Thank you in advance.
 
Hi
try the following array formula (entered with CRL+sHIFT+ENTER)
=AVERAGE(IF((B1:B100=D1)*(C1:C100=E1),A1:A100))
 
thanks frank. the formula returns division by zero. I do
have zero's in column A. what do you think i should do ?
 
Hi
this should only happen if there's no match in col. B+C that is no
number is returned from column A
 
If the conditions are satisfied in some rows, then #DIV/0 probably
indicates that column A contains text rather than numbers. What do you
get for COUNT(A1:A100)?

To convert from text to numbers, select and copy an empty cell, then
select all of A1:A100 and Edit|Paste Special|Add.

Jerry
 
Back
Top