Count of Rows Testing for Multiple Conditions using SUMPRODUCT

I have an Excel spreadsheet which needs to count the number of valid lines (rows).  The spreadsheet has 3 columns:

Plan Type           # Lives            %
PPO                           14           11.8%
PPO                             0                          
HDHP                                        15.2%
PPO                            22             9%
HMO                          25            13.6%

To be counted as a valid line, there must be a number in the lives column, a number in the % column, and the plan type must be either PPO or HDHP.  Just by eyeballing the small chart you can see that the answer is 2 because in row 3 the % is missing which kicks out the row, in row 4 the lives is missing which kicks out the row, and in row 6 the plan type is not either PPO or HDHP which kicks out the row.  That leaves only 2 valid lines.

I came up with what I thought was the right formula to count valid lines which involves a SUMPRODUCT:


The first two argument checking for blanks in the lives and % column seems to function correctly.  It's the third argument which is supposed to isolate if the value in column A is PPO or HDHP which is not right.  It counts it no matter what.  So my formula is returning the value 3 and it should be returning 2 because the last row has "HMO" in column A and it should be kicking that out. Where is my logic wrong?  

I have attached a spreadsheet with the above scenario.
Who is Participating?
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Please try this...


Open in new window

witzph1Author Commented:
That did the trick!  Thank you!!
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome! Glad it worked as desired.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.