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:

=SUMPRODUCT((B2:B6<>"")*(C2:C6<>"")*(OR(A2:A6="PPO",A2:A6="HDHP")))

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.
Valid-Lines.xlsx
witzph1Asked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Please try this...

=SUMPRODUCT((A2:A6={"PPO","HDHP"})*(B2:B6<>"")*(C2:C6<>""))

Open in new window

0
 
witzph1Author Commented:
That did the trick!  Thank you!!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome! Glad it worked as desired.
0
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.