[Karen Day]

Hi there,

I have two columns of data spanning 100 rows

column B can be made up of numerics and alphabetics.
column C is made up of numerics being either 150, 310, 390, or 1100.

I want to count the number of cells in Column C which has 390 entered into it as long as column B has a numeric in the cell.

In a previous Thread - MTB replied back suggesting the following formula which works perfectly:

=SUMPRODUCT(ISNUMBER(B2:B47)*(C2:C47=390))

I want to go a step further, in that I want to find the average of the total numerics in column B for those cells which have 150 entered into the corresponding cell in column C.

So, for example....

Column A Column B
2 390
11 150
17 390
6 1100
9 150
8 390
20 150
blank 150
EXT 150
Ext 390

In this case I have only 3 figures in column A which have 150 entered in column B:
Column A Column B
11 150
9 150
20 150

The sum of Column A (where there is a 150 in column B) is 40 and as only 3 figures make up this total then the average is 13.33



I hope I have made myself clear. Please do not hesitate to contact me if you need any more detail.

Thankyou in advance for any assistance given.

Regards

Karen Day

[MikeTheBike]

Hi me again

How about this

=SUMIF(B2:B11,"=150",A2:A11)/SUMPRODUCT(ISNUMBER(A2:A11)*(B2:B11=150))

??

MTB

[Karen Day]

The formula works perfectly.

Thank you so very much.

Regards

Karen Day