First time poster needs help. =countif consecutive entries help

[WMOTT]

Hey guys so i'm relatively new to excel and think my issue will be relatively easy to solve based off the knowledge on these boards.

So image in cells A1:A5 I have entered the words either "Yes" or "No". Also in cells B1:B5 are entered the words "Yes" or "No".

So basically if two yeses occur in a row (A1=Yes, B1=Yes) that equals 2 or if two no's occur in a row (A1=No, B1=No) that also equals 2. A combination of A1=Yes and B1=No would equal one and vice versa with the opposite combination.

Now I could do this by myself so far but the part where i'm getting tripped up on is the cells below it. A:6 & B6 through however many. If these cells are blank than I would like the corresponding C cell to remain blank as well.

I've been trying to solve this problem using various combinations of the =countif and =IF functions but can't quit figure it out. Basically I need a formula that will return me this data in column C

A B C
Yes Yes 2
Yes No 1
No No 2
" " " " " "

 

[Jonmo1]

COUNT = count of Numbers (will not count Text strings)
COUNTA = count of Non Blanks (any value, Number or Text)
COUNTBLANK = count of Blanks
COUNTIF = count of specific value

So basically this is saying if the count of NON Blanks in A1:B1 = 2, Then
=IF(COUNTA(A1:B1)=2

So only if BOTH A1 and B1 are populated (counta=2) will it return the 1 or 2
If A1 = Yes and B1 is left blank (counta=1), then the IF test is FALSE, and the formula returns ""

 

[Jonmo1]

If you wanted to return the 1 or 2 if Either cell is populated
Try
=IF(COUNTA(A1:B1)>0,(A1=B1)+1,"")

Now this will only return "" if BOTH A1 and B1 are blank.

 

[WMOTT]

Thank you so much! You're not only great with excel but also great at explaining the functions!

 

[WMOTT]

Sorry and last question I promise! What about the exact same scenario but when now a no no sequence is no longer acceptable. Now it can only be yes yes? (For a different column by the way)

 

[Jonmo1]

You could do
=IF(AND(A1="Yes",B1="Yes"),2,"")

 

[WMOTT]

That works awesome. The only problem I have with it is when i'm trying to calculate all of this up. Bascially 2 yeses equals a 2 and that works. But I need it to enter a value in for when the two cells have been populated with data but aren't equal to each other, that way I can calculate an overall percentage at the end. Lets say "A1 and B1 both equal "Yes" and A2 and B2 are a yes/no combination. I need to be able to have a formula for an overall percentage of 50%. So far I have just been using count if functions so I would basically do a =countif(range,2) divided by =counta(same range).

I know technically those formulas would all still work with each other to calculate an overall percentage but i've been using conditional formatting (2 = Green) and (1 = Red) so with it returning a blank cell it wouldn't work if that makes since.

 

[Jonmo1]

Then try
=IF(AND(A1="Yes",B1="Yes"),2,1)

2 if both are Yes, 1 otherwise.

 

[WMOTT]

That was actually the first thing I tried and it still works but the problem is when I drag the formula down to cover all the cells in that column, it puts a 1 into the corresponding cell where no data in columns A and B have been entered in yet if that makes sense.

A B C
Yes Yes 2
No No 1
" " " " 1

 

[Jonmo1]

OK,

=IF(COUNTA(A1:B1)=2,AND(A1="Yes",B1="Yes")+1,"")

or if you want the 1 if A1 is populated and B1 is Blank or vice versa
=IF(COUNTA(A1:B1)>0,AND(A1="Yes",B1="Yes")+1,"")

 

[WMOTT]

You're awesome! I seriously hope they pay you for this!