Average with Criteria

[Sassy]

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

 

[Mike H]

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

 

[bpeltzer]

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

 

[Sassy]

HI,

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

See below

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

Example of data:

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

 

[Stefi]

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:

 

[Teethless mama]

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

ctrl+shift+enter, not just enter


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

Just normal enter

 

[Mike H]

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

Mike

 

[Sassy]

Perfect.... thank you

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

 

[Plodder]

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

 

[Plodder]

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.

 

[T. Valko]

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.