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 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.