Solved

SUMPRODUCT test for blank cells

Posted on 2011-02-28
2
277 Views
Last Modified: 2012-08-14
I've got a spreadsheet in which I need to count the number of names in C12:C309 where column E is not blank and column F does not have "DUP" in it.  I've created a sumproduct formula below:

=SUMPRODUCT(C12:C309<>"")*(E12:E309<>"")*(F12:F309<>"DUP")

This formula should work perfectly in theory, and in fact does do what I want on a "normal" spreadsheet with static values in columns C, E and F.  But in my "real" spreadsheet, the values in Columns C, E and F are pulling from another linked spreadsheet and only "appear" blank or empty through the use of an IF statement (=IF<linkfield>="","",<linkfield>). In other words, all rows 12 to 309 have an IF statement in them.

So somehow I need to have the SUMPRODUCT fn NOT count the row if the calculated value of each field is blank.  Makes sense?
0
Comment
Question by:witzph1
2 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
Comment Utility
Missing some parens:

=SUMPRODUCT((C12:C309<>"")*(E12:E309<>"")*(F12:F309<>"DUP"))

Kevin
0
 

Author Comment

by:witzph1
Comment Utility
Holy cow.  Pretty sad that I didn't catch that.  But thanks for you help.  That was an easy 500 points.  I guess it's 1 point for the parenthesis and 499 for knowing that it was the parenthesis.  
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now