Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Average with Criteria

2 views
Skip to first unread message

Sassy

unread,
May 16, 2008, 8:03:01 AM5/16/08
to
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

unread,
May 16, 2008, 8:12:00 AM5/16/08
to
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

unread,
May 16, 2008, 8:16:00 AM5/16/08
to
=sumif(criteria_range,criteria,sum_range) / countif(criteria_range,criteria)

Sassy

unread,
May 16, 2008, 8:26:01 AM5/16/08
to
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%

Stefi

unread,
May 16, 2008, 8:26:04 AM5/16/08
to
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

unread,
May 16, 2008, 8:41:01 AM5/16/08
to
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

Mike H

unread,
May 16, 2008, 8:46:02 AM5/16/08
to
You would get that error of the numbers you are trying to sume are text
instead of numbers. Check your sum range

Mike

Sassy

unread,
May 16, 2008, 9:03:02 AM5/16/08
to
Perfect.... thank you

Plodder

unread,
Jun 6, 2009, 7:01:01 PM6/6/09
to

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

unread,
Jun 6, 2009, 7:16:01 PM6/6/09
to
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

unread,
Jun 6, 2009, 9:59:57 PM6/6/09
to
>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" <Plo...@discussions.microsoft.com> wrote in message
news:C00A12FD-F553-4C25...@microsoft.com...

0 new messages