need to count/average if...
 

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.

need to count/average if...
 

=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).

"Need Help 123" wrote:

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.

need to count/average if...
 

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?)

"Sloth" wrote:

=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).

"Need Help 123" wrote:

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.

need to count/average if...
 

=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)


"Need Help 123" wrote in message
...
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?)

"Sloth" wrote:

=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).

"Need Help 123" wrote:

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.

need to count/average if...
 

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)))

"Need Help 123" wrote:

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?)

"Sloth" wrote:

=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).

"Need Help 123" wrote:

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.

need to count/average if...
 

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

"Bob Phillips" wrote:


=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)


"Need Help 123" wrote in message
...
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?)

"Sloth" wrote:

=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).

"Need Help 123" wrote:

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.

need to count/average if...
 

Thanks... this one works too!

"Sloth" wrote:

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)))

"Need Help 123" wrote:

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?)

"Sloth" wrote:

=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).

"Need Help 123" wrote:

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.