If Statement Help

[Excel15]

Ok so I have 8 years of year-over-year growth in Free Cash Flow per share for a company and it's sector as a whole. In the attachment cell S6 is supposed to count up the # of times the companies growth rate was better than it's respective sector and score a 1 if it was true and 0 if not. The problem I'm running into is that if a company had no free cash flow in a period the database that I pull the data from just returns a blank cell (Cell C6) and the If statement I'm using still counts that as being greater than the sector if per-se the sectors growth in that period was a negative number (Cell D6). Is there anyway to bypass this issue and put in some other type of argument in my formula in cell S6 so as to score these situations as a 0 and not a 1? Any help would be much appreciated!

Cheers

[63falcondude]

D3 can be changed to this:

=--AND(C6<>"",C6>D6)

Better yet, try this in S6:

=SUMPRODUCT((C6:Q6<>"")*(C6:Q6>D6:R6))

[Excel15]

Ok so the first suggestion using the --AND formula works for that issue, but the sumproduct does not. I've attached a revised example showing what the score should actually be for each year and then the total score in S3

Thank you!

[Excel15]

Is there a way to alter the Sumproduct( formula to do what the first formula is doing for each year?

[63falcondude]

Try

=SUMPRODUCT(($D$5:$R$5="Sector")*(C6:Q6<>"")*(C6:Q6>D6:R6))

[Excel15]

This works great! Thank you

[63falcondude]

You're welcome. Glad to help!