Average with specific criteria
 

I need to average the cells on column b that correspond to the criteria in
column a. If cells in column a match "EDCP", average the cells in column b
that correspond to those cells, not the whole column. In this case it would
equal 00:30. I will appreciate your help.

Order Procedure Drawn to Received
EDCP 0:06
CBCsD 0:06
UA 0:16
EDCP 0:59
CBCsD 0:59
Troponin-I 0:59
EDCP 0:25

--
Clari

Average with specific criteria
 

Try one of these...

All versions of Excel, array entered**:

=AVERAGE(IF(A1:A7="EDCP",B1:B7))

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

Format as [h]:mm

Excel 2007 only, normally entered:

=AVERAGEIF(A1:A7,"EDCP",B1:B7)

Format as [h]:mm

--
Biff
Microsoft Excel MVP


"Clari" wrote in message
...
I need to average the cells on column b that correspond to the criteria in
column a. If cells in column a match "EDCP", average the cells in column b
that correspond to those cells, not the whole column. In this case it
would
equal 00:30. I will appreciate your help.

Order Procedure Drawn to Received
EDCP 0:06
CBCsD 0:06
UA 0:16
EDCP 0:59
CBCsD 0:59
Troponin-I 0:59
EDCP 0:25

--
Clari

Average with specific criteria
 

=SUMIF(A:A,"EDCP",B:B)/COUNTIF(A:A,"EDCP")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Clari" wrote:

I need to average the cells on column b that correspond to the criteria in
column a. If cells in column a match "EDCP", average the cells in column b
that correspond to those cells, not the whole column. In this case it would
equal 00:30. I will appreciate your help.

Order Procedure Drawn to Received
EDCP 0:06
CBCsD 0:06
UA 0:16
EDCP 0:59
CBCsD 0:59
Troponin-I 0:59
EDCP 0:25

--
Clari

Average with specific criteria
 

XL2007

=AVERAGEIF(A2:A8,"EDCP",B2:B8)
just press enter


All versions

=AVERAGE(IF(A2:A8="EDCP",B2:B8))
ctrl+shift+enter, not just enter


"Clari" wrote:

I need to average the cells on column b that correspond to the criteria in
column a. If cells in column a match "EDCP", average the cells in column b
that correspond to those cells, not the whole column. In this case it would
equal 00:30. I will appreciate your help.

Order Procedure Drawn to Received
EDCP 0:06
CBCsD 0:06
UA 0:16
EDCP 0:59
CBCsD 0:59
Troponin-I 0:59
EDCP 0:25

--
Clari

Average with specific criteria
 

SUMIF(A:A,"EDCP",B:B)/COUNTIF(A:A,"EDCP")
--
pleae click yes if it was helpfull
regards from Brazil
Marcelo



"Clari" escreveu:

I need to average the cells on column b that correspond to the criteria in
column a. If cells in column a match "EDCP", average the cells in column b
that correspond to those cells, not the whole column. In this case it would
equal 00:30. I will appreciate your help.

Order Procedure Drawn to Received
EDCP 0:06
CBCsD 0:06
UA 0:16
EDCP 0:59
CBCsD 0:59
Troponin-I 0:59
EDCP 0:25

--
Clari

Average with specific criteria
 

Hi,

and a non-array 2003 version formula:

=SUMPRODUCT(--(A:A=D1),B:B/COUNTIF(A:A,D1))

where D1 contain EDCP

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Clari" wrote:

I need to average the cells on column b that correspond to the criteria in
column a. If cells in column a match "EDCP", average the cells in column b
that correspond to those cells, not the whole column. In this case it would
equal 00:30. I will appreciate your help.

Order Procedure Drawn to Received
EDCP 0:06
CBCsD 0:06
UA 0:16
EDCP 0:59
CBCsD 0:59
Troponin-I 0:59
EDCP 0:25

--
Clari

Average with specific criteria
 

Thanks for all the very helpful responses. You are all great!
--
Clari


"Shane Devenshire" wrote:

Hi,

and a non-array 2003 version formula:

=SUMPRODUCT(--(A:A=D1),B:B/COUNTIF(A:A,D1))

where D1 contain EDCP

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Clari" wrote:

I need to average the cells on column b that correspond to the criteria in
column a. If cells in column a match "EDCP", average the cells in column b
that correspond to those cells, not the whole column. In this case it would
equal 00:30. I will appreciate your help.

Order Procedure Drawn to Received
EDCP 0:06
CBCsD 0:06
UA 0:16
EDCP 0:59
CBCsD 0:59
Troponin-I 0:59
EDCP 0:25

--
Clari

Average with specific criteria
 

=SUMPRODUCT(--(A:A=D1),B:B/COUNTIF(A:A,D1))

You can not use a whole column prior to xl-2007


"Shane Devenshire" wrote:

Hi,

and a non-array 2003 version formula:

=SUMPRODUCT(--(A:A=D1),B:B/COUNTIF(A:A,D1))

where D1 contain EDCP

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Clari" wrote:

I need to average the cells on column b that correspond to the criteria in
column a. If cells in column a match "EDCP", average the cells in column b
that correspond to those cells, not the whole column. In this case it would
equal 00:30. I will appreciate your help.

Order Procedure Drawn to Received
EDCP 0:06
CBCsD 0:06
UA 0:16
EDCP 0:59
CBCsD 0:59
Troponin-I 0:59
EDCP 0:25

--
Clari