need to count/average if...

[Guest]

I want a function that will 1st off only look at numbers if they are next to
a cell with the label "case summary". 2nd I want an average of those numbers,
but I dont want to count any cells that have no number entered in them at
all... but I do want to count all cells that have numbers, including the
number 0% entered in them.

I tried using the following but I think it may be counting cells with no
numbers in them and thus lowering the average??? or maybe its a different
problem?

=AVERAGE(IF(B2:B124="Case Summary",C2:C124, "")) and hit control shift enter.

 

[Guest]

=SUMPRODUCT(C2:C124,--(B2:B124="Case
Summary"))/SUMPRODUCT(ROW(C2:C124)/ROW(C2:C124),--(B2:B124="Case Summary"))

this should work for you (works without hitting ctrl+shift+enter).

 

[Guest]

I dont understand... when I average the cells that it should be counting I
get a higher % than with the formula (I get a % in the 50 range and it gives
me a % in the 17 range?)

 

[Bob Phillips]

=AVERAGE(IF((B2:B124="Case Summary")*(C2:C124<>""),C2:C124, ""))

again as an array formula

--

HTH

RP
(remove nothere from the email address if mailing direct)

I dont understand... when I average the cells that it should be counting I
get a higher % than with the formula (I get a % in the 50 range and it gives
me a % in the 17 range?)

shift enter.

 

[Guest]

I forgot to check for empty cells in the count section. Sorry, try this.

=SUMPRODUCT(C2:C124,--(B2:B124="CaseSummary"))/SUMPRODUCT(ROW(C2:C124)/ROW(C2:C124),--(B2:B124="Case Summary"),--(ISNUMBER(C2:C124)))

 

[Guest]

That worked... thanks... this site is a life saver for me.....thanks everyone
for your help.

 

[Guest]

Thanks... this one works too!

I forgot to check for empty cells in the count section. Sorry, try this.

=SUMPRODUCT(C2:C124,--(B2:B124="CaseSummary"))/SUMPRODUCT(ROW(C2:C124)/ROW(C2:C124),--(B2:B124="Case Summary"),--(ISNUMBER(C2:C124)))