Question description:
This user has given permission to use the problem statement for this
blog.
I have a 3 column table showing a list of article titles, article subheadings and a yes/no column. An article is complete if all subheadings have a corresponding 'yes'. There are mulitple subheadings per article title. If there a formula to count the number of article titles with all corresponding subheadings marked as 'yes'?
Solved by K. W. in 42 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
14/05/2018 - 03:12
Hello, I understand that you need help in counting the number of subheadings with a YES per article, right?
Excelchat Expert
14/05/2018 - 03:14
Are you there?
User
14/05/2018 - 03:14
Hi there. I think that's it. To clarify, I am looking to count the number of articles with YES for all their associated subheadings. i.e. if an article has 4 subheadings, all with YES, that counts as 1
Excelchat Expert
14/05/2018 - 03:15
Ah I see.
Excelchat Expert
14/05/2018 - 03:15
I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.
Excelchat Expert
14/05/2018 - 03:15
Are you able to provide a sample data so I can create an accurate formula for you?
User
14/05/2018 - 03:15
sure
Excelchat Expert
14/05/2018 - 03:15
Thank you. That would make things a lot easier.
Excelchat Expert
14/05/2018 - 03:17
Where do you want the result to be placed?
User
14/05/2018 - 03:18
any cell works for me
Excelchat Expert
14/05/2018 - 03:19
Alright so you just want to figure out which Title has at least one Yes, right?
User
14/05/2018 - 03:20
I need to know which title has a YES by ALL of it's associated subheadings
Excelchat Expert
14/05/2018 - 03:20
I see.
User
14/05/2018 - 03:20
Sorry- I don't need to know WHICH
User
14/05/2018 - 03:20
I need to know HOW MANY
Excelchat Expert
14/05/2018 - 03:20
Okay, that certainly complicates things a bit but let me try.
User
14/05/2018 - 03:20
so in this case it would be 1 title (worst tower at school)
Excelchat Expert
14/05/2018 - 03:21
Is a helper column fine with you?
Excelchat Expert
14/05/2018 - 03:22
We might need to add helper columns. They are columns that help us get an accurate count of what you need. They can be hidden.
User
14/05/2018 - 03:22
yeah that's fine
Excelchat Expert
14/05/2018 - 03:24
Don't mind what you are seeing right now, I'm just analyzing it.
User
14/05/2018 - 03:24
ah, okay- no worries
Excelchat Expert
14/05/2018 - 03:28
So column D is the helper column, you can hide it. The formula basically checks if the number of YES is equals to the number of subheadings, which would mean that the title is completed.
User
14/05/2018 - 03:29
brilliant- thank
User
14/05/2018 - 03:29
am okay to review this quickly when you are on the line? Just to make sure I understand?
Excelchat Expert
14/05/2018 - 03:29
Note that the formula in the Helper column needs to be applied to all rows.
Excelchat Expert
14/05/2018 - 03:29
Sure.
User
14/05/2018 - 03:29
thanks
Excelchat Expert
14/05/2018 - 03:38
How is it?
User
14/05/2018 - 03:40
I've just tried applying the helper column to my spreadsheet and it's not working unfortunately- I'm a bit lost here- please could you run through each section of the formula in detail?
Excelchat Expert
14/05/2018 - 03:41
Okay, basically, the formula must be written in a very special way. Even the $ in the first countifs must be accurate.
Excelchat Expert
14/05/2018 - 03:41
Can you copy and paste your formula here?
User
14/05/2018 - 03:41
sure
User
14/05/2018 - 03:41
=if(countifs($G$7:G7,G72)=1,countifs(G:G,G2)=countifs(G:G,G2,AM:AM,"YES"),0)
User
14/05/2018 - 03:42
(my column A is now column G and my column C is now column AM)
Excelchat Expert
14/05/2018 - 03:42
Please look at the first countifs.
Excelchat Expert
14/05/2018 - 03:42
It says G72 instead of just G7
User
14/05/2018 - 03:45
so I've updated the G7
Excelchat Expert
14/05/2018 - 03:45
Can you paste your new formula here?
User
14/05/2018 - 03:45
and the formula says TRUE is though not all YESs
User
14/05/2018 - 03:45
sure
User
14/05/2018 - 03:46
=if(countifs($G$7:G7,G7)=1,countifs(G:G,G2)=countifs(G:G,G2,AM:AM,"YES"),0)
Excelchat Expert
14/05/2018 - 03:46
so column AM is where YES and NO's are?
User
14/05/2018 - 03:46
yes
Excelchat Expert
14/05/2018 - 03:46
Because your formula is referencing G7 in one and then G2 in the other.
Excelchat Expert
14/05/2018 - 03:46
They have to be the same.
Excelchat Expert
14/05/2018 - 03:46
It's either G7 or G2 in all.
Excelchat Expert
14/05/2018 - 03:47
What row does your formula start?
User
14/05/2018 - 03:48
Row 7- sorry
User
14/05/2018 - 03:48
updated the formula
User
14/05/2018 - 03:49
=if(countifs($G$7:G7,G7)=1,countifs(G:G,G7)=countifs(G:G,G2,AM:AM,"YES"),0)
Excelchat Expert
14/05/2018 - 03:49
You haven't. :)
Excelchat Expert
14/05/2018 - 03:49
I can still see G2.
Excelchat Expert
14/05/2018 - 03:49
What column is your helper column?
User
14/05/2018 - 03:49
=if(countifs($G$7:G7,G7)=1,countifs(G:G,G7)=countifs(G:G,G7,AM:AM,"YES"),0)
User
14/05/2018 - 03:50
Sorry it's now working!
Excelchat Expert
14/05/2018 - 03:50
Yes as it should. :)
User
14/05/2018 - 03:50
apologies for being moronic
User
14/05/2018 - 03:50
thanks very much for your help
Excelchat Expert
14/05/2018 - 03:50
So I have a bit of explanation in columns EFG.
Excelchat Expert
14/05/2018 - 03:50
I broke down the formula for you there.
Excelchat Expert
14/05/2018 - 03:50
The first part is the checker. This checks if this is the first instance of the title because we only want to do this once per title.
Excelchat Expert
14/05/2018 - 03:51
If it returns true then we want to know if the total subheadings is equal to the number of YES.
Excelchat Expert
14/05/2018 - 03:51
If it is then we count it, if not then we don't.
Excelchat Expert
14/05/2018 - 03:51
If you see that all the FALSE in column E says we don't care in column H. This is so we don't count the same title multiple times.
Excelchat Expert
14/05/2018 - 03:52
We only count one title once if Total subheadings is equal to the number of YES which signifies complete.
Excelchat Expert
14/05/2018 - 03:52
You may want to download the file to the right to use as a reference.
User
14/05/2018 - 03:52
that's really useful- thanks
Excelchat Expert
14/05/2018 - 03:53
Would there be anything else that I can help you with regards to the original question?
User
14/05/2018 - 03:53
no thanks- this has been great
Excelchat Expert
14/05/2018 - 03:53
I'm glad to have helped!
Excelchat Expert
14/05/2018 - 03:53
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
14/05/2018 - 03:53
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
14/05/2018 - 03:53
Thank you for contacting Got It Pro. Have an awesome day!
User
14/05/2018 - 03:53
sure- no problem- 5 star rating on it's way
User
14/05/2018 - 03:54
thanks!
Excelchat Expert
14/05/2018 - 03:54
Thank you I'd appreciate that. It would help me a lot. Please do not forget to click the End Session button so you can rate me.
User
14/05/2018 - 03:54
sure
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
