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

Hi me again

How about this

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

??

MTB

The formula works perfectly.

Thank you so very much.

Regards

Karen Day