Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc