# Thread: average and sumproduct

1. Registered User
Join Date
May 2002
Posts
157

## Unanswered: average and sumproduct

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

2. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
802
Provided Answers: 2
Hi me again

How about this

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

??

MTB

3. Registered User
Join Date
May 2002
Posts
157
The formula works perfectly.

Thank you so very much.

Regards

Karen Day

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•