averageif with 2 other criteria

V

v1rt8

Need some help with an averageif calc.
setup is as follows:
q3:q1357 contains ages
r3:r1357 contains text (active), (MGT), (others)

so averageif q3:q1357 active and mgt in 43:r1357
so averageif

looked at other questions in here and cannot find anything close
thanks in advance
 
V

v1rt8

v1rt8 said:
Need some help with an averageif calc.
setup is as follows:
q3:q1357 contains ages
r3:r1357 contains text (active), (MGT), (others)

so averageif q3:q1357 active and mgt in r3:r1357
so averageif q3:q1357 active in r3:r1357
Click to expand...
 
T

T. Valko

Are you using Excel 2007?
so averageif q3:q1357 active and mgt in r3:r1357
so averageif q3:q1357 active in r3:r1357
Click to expand...

Are those 2 separate requests?

Get the average age if column R contains either active *or* mgt.

Get the average age if column R contains active.
 
S

Sean Timmons

=SUMIF(r3:r1357,"active",q3:q1357)/countif(r3:r1357,"active")

Then
=(SUMIF(r3:r1357,"active",q3:q1357)+SUMIF(r3:r1357,"mgt",q3:q1357))/(countif(r3:r1357,"active")+countif(r3:r1357,"mgt"))
 
V

v1rt8

Yes i am, they are two separate requests,
in one cell the avg age of active and mgt
in another cell the avg age of active
in another cell the avg age of mgt
thanks, i should have been more clear
 
V

v1rt8

would this give me the avg age?

Sean Timmons said:
=SUMIF(r3:r1357,"active",q3:q1357)/countif(r3:r1357,"active")

Then,
=(SUMIF(r3:r1357,"active",q3:q1357)+SUMIF(r3:r1357,"mgt",q3:q1357))/(countif(r3:r1357,"active")+countif(r3:r1357,"mgt"))
Click to expand...
 
S

Sean Timmons

It would give the mean average, which is what AVERAGe in Excel provides. You
are adding together the total of ages, then dividing by the number of people.
 
V

v1rt8

thank you sir, i appreciate your time and effort

Sean Timmons said:
It would give the mean average, which is what AVERAGe in Excel provides. You
are adding together the total of ages, then dividing by the number of people.
Click to expand...
 
V

v1rt8

thank you sir for your time and effort

Sean Timmons said:
It would give the mean average, which is what AVERAGe in Excel provides. You
are adding together the total of ages, then dividing by the number of people.
Click to expand...
 
T

T. Valko

Try these...

Use cells to hold the criteria.

T3 = active
T4 = mgt

Enter this formula in U3 and copy down to U4:

=AVERAGEIF(R$3:R$1357,T3,Q$3:Q$1357)

For the combined average of active *or* mgt...

Array entered** :

=AVERAGE(IF(ISNUMBER(MATCH(R3:R1357,T3:T4,0)),Q3:Q1357))

** 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.
 
Top