COUNTIF and SUMIF in Excel 2

[Mark2Aus]

I need help as I can't get this right.
I want to count how many prices in a large range (C17:C3215) are higher than the average price of the range and I also want to calculate the average price of those prices above the average price of the range.

I thought I knew!!

My formula =COUNTIF(C17:C3215,">AVERAGEA(C17:C3215)") returns zero. Why?
The same with =SUMIF(C17:C3215,">AVERAGEA(C17:C3215)").
I know we can use both functions when the criteria is text or values, but can't we use them when the criteria is another function or a formula?
Thanks
Mark

 

[SQLScholar]

With

=COUNTIF(C17:C3215,">AVERAGEA(C17:C3215)")

Try removing the " " - as this means its text rather the the averagea function so...

=COUNTIF(C17:C3215,>AVERAGEA(C17:C3215))

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[TonyJollans]

Hi Mark2Aus,

The COUNTIF Function does want a string argument for the criteria, but you want that string to include the result of the AVERAGEA Function.

You must build the string up with the literal ">" and average, ...

[blue][tt] =COUNTIF(C17:C3215,[/tt][red][tt]">"[/tt][/red][green][tt]&[/tt][/green][purple][tt]AVERAGEA(C17:C3215)[/tt][/purple][tt])[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[jonsi]

thanks Tony

...valuable lesson re-learned!

have a STAR!

 

[TonyJollans]

Thanks, jonsi, most kind!

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.