First time poster needs help. =countif consecutive entries help

W

WMOTT

New Member
Joined
Jun 23, 2014
Messages
17
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
" " " " " "
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Welcome to the board

Try
=AND(A1<>"",B1<>"",A1=B1)+1

Wait, that will be 1 when A1/B1 is blenk..
Hang on.
 
Upvote 0
That is very close. Now I just need a way to keep the C column cell blank when the corresponding A & B cells are blank as well.
 
Upvote 0
Jonmo1 said:
Try
=IF(COUNTA(A1:B1)=2,(A1=B1)+1,"")
Click to expand...

It worked! You're the man! Thank you so much I spent the last 2 hours on that! (Like I said i'm very new to excel). And i'm from College Station, TX so from one Aggie to most likely another. Thanks and Gig'em!!
 
Upvote 0
Can you explain to me in words how that function works. I know how to use and IF function and I know =counta only counts cells that contain some sort of information typed into it but the (A1=B1)+1 part is where I start getting confused. I only ask so I can truly learn and be able to do this myself the next time.
 
Upvote 0
Sure..

(A1=B1) is an expression, or a Question if you will.
And the expression has either a True or False answer.
Does A1 = B1, true or false?

So say A1=Yes and B1=Yes
so you have
("Yes"="Yes")+1
(TRUE)+1

When doing a math operation like + - / * on a True/False result.
True = 1, False = 0
so it's
(1)+1 = 2


If A1=Yes and B1=No then it's

("Yes"="No")+1
(False)+1
(0)+1 = 1

Hope that helps.
 
Upvote 0
I get it and absolutely understand it. That will really help me in the future with my IF functions turning words into quantitative data I can work with. Thank you again so much!
 
Upvote 0
Sorry but just to make sure I understand it completely can you walk me through the logical test you conducted on the IF function.

Basically the "counta(A1:B1)=2,"

I know counta only counts cells with entered data but that about as far as I understand. I know with =count it simply counts the number of cells with entered data. =countif lets you find certain data out of a list but previously i've just been using the =counta function as out of this incomplete list, how many cells are completed so far. I've never had it equal anything in a logical test of an IF function.

Thanks for the help again. I'm very new to excel but for some reason all of this is really exciting for me to learn!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Excel Consecutive Formula
2
Replies
10
Views
327
mahmed1
M
J
  • Question
Help needing two cells equal a constant value.
Replies
3
Views
148
JEC
JEC
I
  • Question
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
253
ItalianPlatinum
I
N
  • Question
Help merging two pieces of VBA code
Replies
8
Views
482
Nordicrx8
N
S
  • Question
Offset formula (I think) help needed
Replies
3
Views
248
Fluff
Fluff

Forum statistics

Threads
1,214,893
Messages
6,122,118
Members
449,066
Latest member
Andyg666

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top