# =sumproduct (for weighted average) -- need it to stop counting blank cells.

1. ## =sumproduct (for weighted average) -- need it to stop counting blank cells.

The formula I typed is
=SUMPRODUCT(E2:E17,D2:D17/SUM(D2:D17))

Works fine but gives me a lowered average because it counts blank cells as a 0%.

I want it to count only those cells that have the % value in them.

2. ## Re: =sumproduct (for weighted average) -- need it to stop counting blank cells.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

3. ## Re: =sumproduct (for weighted average) -- need it to stop counting blank cells.

Perhaps add another criteria, something like...
E2:E17<>0

4. ## Re: =sumproduct (for weighted average) -- need it to stop counting blank cells.

Didn't work...
@Pepe le Mokko sorry; FDibbin's add-another-criteria didn't work for me. When I tried doing that I just got 0.00%, but it's probably my limited knowledge of Excel.

5. ## Re: =sumproduct (for weighted average) -- need it to stop counting blank cells.

g2=SUMPRODUCT((\$B\$3:\$B\$20="Section 1")*(\$D\$3:\$D\$20))/SUMPRODUCT((\$B\$3:\$B\$20="Section 1")*(\$A\$3:\$A\$20))

See the attached file.

6. ## Re: =sumproduct (for weighted average) -- need it to stop counting blank cells.

Try

F2: =SUMPRODUCT(B\$2:B\$17,A\$2:A\$17)/SUMIF(B\$2:B\$17,">0",A\$2:A\$17)
F3: =SUMPRODUCT(C\$2:C\$17, A\$2:A\$17)/SUMIF(\$C\$2:\$C\$17,">0",A\$2:A\$17)

7. ## Re: =sumproduct (for weighted average) -- need it to stop counting blank cells.

Originally Posted by spbone
Didn't work...
I love this kind of non-information. Keeps us guessing as to what did not work

9. ## Re: =sumproduct (for weighted average) -- need it to stop counting blank cells.

Originally Posted by Jonmo1
Try

F2: =SUMPRODUCT(B\$2:B\$17,A\$2:A\$17)/SUMIF(B\$2:B\$17,">0",A\$2:A\$17)
F3: =SUMPRODUCT(C\$2:C\$17, A\$2:A\$17)/SUMIF(\$C\$2:\$C\$17,">0",A\$2:A\$17)
Thanks to everyone who replied.

10. ## Re: =sumproduct (for weighted average) -- need it to stop counting blank cells.

You're welcome.

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1