# 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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel & VBA ExpertCommented:

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

Experts Exchange Solution brought to you by