Conditional Formulas For Averaging

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a formula that will look for a particular condition in
one column, and if that condition exists, then average the numbers from a
different column for the same row. As an example, one column contain the
names of multiple sales people, the other column contains the number of units
each one sold each day. I want the formula to average the number of units
sold for each person's name. So, if "Joe" sold 10 units one day and 20 units
another day, the formula would indicate that Joe sold 15 units.
 
=SUMPRODUCT(--(A1:A1000="Joe"),B1:B1000)/SUMPRODUCT(--(A1:A1000="Joe"))

or

=AVERAGE(IF(A1:A1000="Joe",B1:B1000))

The second is an array formula, so is committed with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
I am trying to create a formula that will look for a particular condition in
one column, and if that condition exists, then average the numbers from a
different column for the same row. As an example, one column contain the
names of multiple sales people, the other column contains the number of units
each one sold each day. I want the formula to average the number of units
sold for each person's name. So, if "Joe" sold 10 units one day and 20 units
another day, the formula would indicate that Joe sold 15 units.
Click to expand...

Here's one way:

Column of Names is named 'Names'
Column of Units is named 'Units'

=SUMIF(Names,"Joe",Units) / COUNTIF(Names, "Joe")

"Joe" (without the quotes) can be in a cell, also.


--ron
 
Back
Top