I would kike to calculate the average in a column if it meets a criteria in
another column. I have tried Average(if(....... this is not working.

I need to know the average probability of sales against the sales executives

Try this

=SUMPRODUCT((A1:A100="a")*(B1:B100))/COUNTIF(A1:A100,"a")

Where 'a' is the name of your sales exec and Column B is what to sum.

Mike

"Sassy" wrote:

I would kike to calculate the average in a column if it meets a criteria in
another column. I have tried Average(if(....... this is not working.

I need to know the average probability of sales against the sales executives

=sumif(criteria_range,criteria,sum_range) / countif(criteria_range,criteria)

"Sassy" wrote:

I would kike to calculate the average in a column if it meets a criteria in
another column. I have tried Average(if(....... this is not working.

I need to know the average probability of sales against the sales executives

HI,

No this is not working I am getting a #VALUE! error

See below

=SUMPRODUCT((A1:A100="George de Bon")*(D1:D100))/COUNTIF(A1:A100,"George de
Bon")

Example of data:

Program Manager Customer Contact Opportunity Value Probability
George de Bon TimTaylor R 300,000 50%


"Mike H" wrote:

Try this

=SUMPRODUCT((A1:A100="a")*(B1:B100))/COUNTIF(A1:A100,"a")

Where 'a' is the name of your sales exec and Column B is what to sum.

Mike

"Sassy" wrote:

I would kike to calculate the average in a column if it meets a criteria in
another column. I have tried Average(if(....... this is not working.

I need to know the average probability of sales against the sales executives

Try something lika this, adjust references:

=SUMIF(B2:B10,1,A2:A10)/=COUNTIF(B2:B10,1)
In this example A2:A10 contains numbers to abe averaged, B2:B10 contains
values to evaluate, the formula return average of A2:A10 where B2:B10 =1

Regards,
Stefi


€žSassy€� ezt Ã*rta:

I would kike to calculate the average in a column if it meets a criteria in
another column. I have tried Average(if(....... this is not working.

I need to know the average probability of sales against the sales executives

All version of Excel:
=AVERAGE(IF(A1:A100="George de Bon",D1:D100))

ctrl+shift+enter, not just enter


For Excel 2007:
=AVERAGEIF(A1:A100,"George de Bon",D1:D100)

Just normal enter


"Sassy" wrote:

HI,

No this is not working I am getting a #VALUE! error

See below

=SUMPRODUCT((A1:A100="George de Bon")*(D1:D100))/COUNTIF(A1:A100,"George de
Bon")

Example of data:

Program Manager Customer Contact Opportunity Value Probability
George de Bon TimTaylor R 300,000 50%


"Mike H" wrote:

Try this

=SUMPRODUCT((A1:A100="a")*(B1:B100))/COUNTIF(A1:A100,"a")

Where 'a' is the name of your sales exec and Column B is what to sum.

Mike

"Sassy" wrote:

I would kike to calculate the average in a column if it meets a criteria in
another column. I have tried Average(if(....... this is not working.

I need to know the average probability of sales against the sales executives

You would get that error of the numbers you are trying to sume are text
instead of numbers. Check your sum range

Mike

"Sassy" wrote:

HI,

No this is not working I am getting a #VALUE! error

See below

=SUMPRODUCT((A1:A100="George de Bon")*(D1:D100))/COUNTIF(A1:A100,"George de
Bon")

Example of data:

Program Manager Customer Contact Opportunity Value Probability
George de Bon TimTaylor R 300,000 50%


"Mike H" wrote:

Try this

=SUMPRODUCT((A1:A100="a")*(B1:B100))/COUNTIF(A1:A100,"a")

Where 'a' is the name of your sales exec and Column B is what to sum.

Mike

"Sassy" wrote:

I would kike to calculate the average in a column if it meets a criteria in
another column. I have tried Average(if(....... this is not working.

I need to know the average probability of sales against the sales executives

Perfect.... thank you

"bpeltzer" wrote:

=sumif(criteria_range,criteria,sum_range) / countif(criteria_range,criteria)

"Sassy" wrote:

I would kike to calculate the average in a column if it meets a criteria in
another column. I have tried Average(if(....... this is not working.

I need to know the average probability of sales against the sales executives

One problem which I am running into the averageif function is that the
criteria cannot be a function of the desired cell, for example
AVERAGEIF(A1:A10,MID(A1:A10,3,1)="A",B1:B10)
comes as an error.
I would like to find the average of all the B columns for which the third
letter of the A column is A, is that possible?
Thanks

One tedious way of overcoming this is to enter each of the rows individually
in the function and using an
AVERAGE(IF(MID(A1,3,1)="A",B1,XX),IF(MID(A2,3,1)=" A",B2,XX...etc)
However here again I am running into the problem of requiring a filler
instead of XX which doesn't act as 0 and abnormally decrease the average. I
have tried "", " " and it miraculously worked in one sheet but not in the
other!!!
Or is the only solution is to do a sum of all the parts from 1 to 10 and
then a countif from 1 -10 and repeat the function...........that is too
painful to even think of.

I would like to find the average of all the B columns
for which the third letter of the A column is A

Try this array formula** :

=AVERAGE(IF(MID(A1:A10,3,1)="A",B1:B10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Plodder" wrote in message
...

One problem which I am running into the averageif function is that the
criteria cannot be a function of the desired cell, for example
AVERAGEIF(A1:A10,MID(A1:A10,3,1)="A",B1:B10)
comes as an error.
I would like to find the average of all the B columns for which the third
letter of the A column is A, is that possible?
Thanks