Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: SUMPRODUCT - ISNUMBER combination

1. Member
Join Date
10th May 2005
Posts
65

## SUMPRODUCT - ISNUMBER combination

Hi,

I am trying to do a weighted average of two items. But I want to omit items that have a non-numeric entry in the fields. I am able to do a sum of the list using the conditions (A8 in the sample). but, the weighted average is not working (cell B8).

Extra step is the checklist for me.

I appreciate any help I can get or any other startegy that would work better.

Thank you

Chandra

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: SUMPRODUCT - ISNUMBER combination

If your actual data contains no zeros (that should be included in teh average) then change B2 to
=SUM(B2:B6)/COUNTIF(B2:B6,">0")

No need for the extra steps nor for the array formula.
Last edited by thomach; February 9th, 2006 at 04:18. Reason: modify

3. ## Re: SUMPRODUCT - ISNUMBER combination

Why not just use

=AVERAGE(B2:B6)

4. ## Re: SUMPRODUCT - ISNUMBER combination

I wasn't thinking fully. Better is simply
=SUM(B2:B6)/COUNT(B2:B6)

COUNT will ignore text and empty cells. It counts only numbers. Similarly, SUM doesn't care about (ignores) text entries.

EDIT. Bob has it right. I was thinking AVERAGE didn't like text strings, but didn't check myself. Obviously I was wrong.
Last edited by thomach; February 9th, 2006 at 04:26. Reason: Bow to Bob

5. ## Re: SUMPRODUCT - ISNUMBER combination

BTW, you don't need to array enter a SUMPRODUCT formula. For instance, in A8 you can simply use

=SUMPRODUCT(--(ISNUMBER(B2:B6)),A2:A6)

6. Member
Join Date
10th May 2005
Posts
65

## Re: SUMPRODUCT - ISNUMBER combination

Hi,

Thank you for everybody who posted in response to my question.

Yes, definitely the suggestions were much simpler than the one I had used.

But my main issue is getting a weighted average of the first two columns - with non-numberic characters in the second column (b2:b6).

The sumproduct seems to give error because of non-numeric values. Maybe it was not clear with my using 1 for all the values in a2:a6. Now, I have changed the numbers.

Hope this makes it a bit more clearer than before.

Chandra

Excel Video Tutorials / Excel Dashboards Reports

7. Member
Join Date
10th May 2005
Posts
65

## Re: SUMPRODUCT - ISNUMBER combination

Hi,

My apologies. Forgot to post the file. B8 is what I have issues with.

Thank you

Chandra

Excel Video Tutorials / Excel Dashboards Reports

8. Biz
Established Member
Join Date
18th January 2006
Posts
524

## Re: SUMPRODUCT - ISNUMBER combination

Hi,

I know another way of solving the problem. Review the attachment and let me know if you get your desired result.

Biz

Excel Video Tutorials / Excel Dashboards Reports

9. Member
Join Date
10th May 2005
Posts
65

## Re: SUMPRODUCT - ISNUMBER combination

Biz,

Thank you for the details.

The sum of Column B is good but the sum of column A is including row 6 which should be eliminated from the SUM of Column A (because the row in column B has a non-numeric charcater).

My desired result is in D8 and the column D does the same thing with an extra step added. But I would like to get this done in 1 step - using columns A and B only.

Hope this makes sense.

Thank you,

Biz

Excel Video Tutorials / Excel Dashboards Reports

10. Super Moderator
Join Date
4th July 2004
Location
Posts
2,371

## Re: SUMPRODUCT - ISNUMBER combination

Try...

=SUM(IF(ISNUMBER(B2:B6),(A2:A6)*(B2:B6)))/SUM(IF(ISNUMBER(B2:B6),A2:A6))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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