Using a cell reference for Sumif Criteria

I am using the following formula to get an average for a
column of data where the values are greater than or equal
to the value 5510.

=SUMIF(C18:C80,"=5510",C18:C80)/COUNTIF(C18:C80,"=5510")

How can I update the formula so that instead of using a
hardcoded value, the formula uses a cell reference for the
criteria?

Try

"="&Cell


=SUMIF(C18:C80,"="&B2,C18:C80)/COUNTIF(C18:C80,"="&B2)

or

=AVERAGE(IF(C18:C80=B2,C18:C80))

the last one entered with ctrl + shift & enter

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

"Kevin" wrote in message
...
I am using the following formula to get an average for a
column of data where the values are greater than or equal
to the value 5510.

=SUMIF(C18:C80,"=5510",C18:C80)/COUNTIF(C18:C80,"=5510")

How can I update the formula so that instead of using a
hardcoded value, the formula uses a cell reference for the
criteria?

Hi Kevin,

Assuming that the cell you want to reference is A1, try,

=SUMIF(C18:C80,"="&A1)/COUNTIF(C18:C80,"="&A1)

or, alternatively, you can try,

=AVERAGE(IF(C18:C80=A1,C18:C80)), to be entered using CTRL+SHIFT+ENTER

Hope this helps!

In article ,
"Kevin" wrote:

I am using the following formula to get an average for a
column of data where the values are greater than or equal
to the value 5510.

=SUMIF(C18:C80,"=5510",C18:C80)/COUNTIF(C18:C80,"=5510")

How can I update the formula so that instead of using a
hardcoded value, the formula uses a cell reference for the
criteria?

Cool...these are great options!

Thanks for your help Dominic and Peo!
-----Original Message-----
Hi Kevin,

Assuming that the cell you want to reference is A1, try,

=SUMIF(C18:C80,"="&A1)/COUNTIF(C18:C80,"="&A1)

or, alternatively, you can try,

=AVERAGE(IF(C18:C80=A1,C18:C80)), to be entered using
CTRL+SHIFT+ENTER

Hope this helps!

In article ,
"Kevin" wrote:

I am using the following formula to get an average for
a
column of data where the values are greater than or
equal
to the value 5510.

=SUMIF(C18:C80,"=5510",C18:C80)/COUNTIF
(C18:C80,"=5510")

How can I update the formula so that instead of using a
hardcoded value, the formula uses a cell reference for
the
criteria?
.