Excel - COUNT Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need a formula that would check for various criteria in a range of cells and count the ones that meet the required criteria
Solved by T. S. in 54 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 23/09/2018 - 04:31
Hello
Excelchat Expert 23/09/2018 - 04:31
Based on the descreption.. you need to count the cells which meets the creiteria..
User 23/09/2018 - 04:31
is there a way for us to talk on slkype or something?
Excelchat Expert 23/09/2018 - 04:32
no. the discussion needs to happen via. chat
Excelchat Expert 23/09/2018 - 04:32
Can you provide us the details of the data and the criteria..
User 23/09/2018 - 04:33
Column F
User 23/09/2018 - 04:34
I need a formula that:will evaluate if Guest 1 thru 5 are not empty and if they're not, then evaluate Guest RSVP Status for e
User 23/09/2018 - 04:34
disregard that
Excelchat Expert 23/09/2018 - 04:35
ok.. :)
Excelchat Expert 23/09/2018 - 04:37
Hi.. are you there?
User 23/09/2018 - 04:38
I need a formula that first compares today's date to Info Session date and if it's equal to or less, then checks Guest 1 thru 5 if they're not empty, and if they're not then to check if Guest RSVP Status is either "Confirmed" or "Show" and if all that is true then to count the instances in each row. But if today's date is greater than "Info Session Date", then to do the same thing but only if GUEST RSVP STATUS is "Show"
User 23/09/2018 - 04:38
that formula I need it in column F
Excelchat Expert 23/09/2018 - 04:39
Ok... give me a minute understand the requirement.. and I'll come back to you..
User 23/09/2018 - 04:39
ok
Excelchat Expert 23/09/2018 - 04:43
So date< today count if guest 1-5 not and empty and RSVP status as confirmed or show right?
User 23/09/2018 - 04:44
date less or equal to
Excelchat Expert 23/09/2018 - 04:44
do i count the number of rows or sum of Total column?
User 23/09/2018 - 04:45
I need a total count of guests for each member
User 23/09/2018 - 04:45
Also, if guest is not empty but RSVP status is, then to ignore that guest
Excelchat Expert 23/09/2018 - 04:45
ok that means sum of the total right?
Excelchat Expert 23/09/2018 - 04:45
ok
User 23/09/2018 - 04:45
yes I think I understand what you mean
User 23/09/2018 - 04:46
ok let me give you an example that would clarify it for you
Excelchat Expert 23/09/2018 - 04:46
But if you see row 1 GUEST 2 - FULL NAME is empty but toatal is 2
User 23/09/2018 - 04:47
forget about the total on the far right
Excelchat Expert 23/09/2018 - 04:47
Yes an example would be good
User 23/09/2018 - 04:47
I need the total in Column F
Excelchat Expert 23/09/2018 - 04:47
ok
Excelchat Expert 23/09/2018 - 04:49
ok
User 23/09/2018 - 04:49
ok in this example Colum F should show 3 if the today's date is equal or less then session date. But if the today's date is greater than session date then it should show 2
Excelchat Expert 23/09/2018 - 04:51
you mean f2 right
User 23/09/2018 - 04:51
guest 1 is ignored because his RSVP status is blank and guest 5 is also ignored because here status is NO ShOW
Excelchat Expert 23/09/2018 - 04:51
yes got it
User 23/09/2018 - 04:51
yes F2
Excelchat Expert 23/09/2018 - 04:51
so formula goes in column F
Excelchat Expert 23/09/2018 - 04:51
Give me few min to work on the formula
User 23/09/2018 - 04:52
ok, brb. have to use the bathroom
Excelchat Expert 23/09/2018 - 04:52
:) no issue after 18 min you have click on extension
User 23/09/2018 - 04:55
back
Excelchat Expert 23/09/2018 - 04:55
ok just a quick question.. todays date is less than session date means that session date is before today right
User 23/09/2018 - 04:56
no it means that session date is after today
Excelchat Expert 23/09/2018 - 04:56
oh ok
User 23/09/2018 - 04:56
after session date I'm only interested in those who showed.
Excelchat Expert 23/09/2018 - 04:57
ok..
User 23/09/2018 - 04:57
Before or on session date I want those who are confirmed or Show
Excelchat Expert 23/09/2018 - 04:57
Yes got it
Excelchat Expert 23/09/2018 - 04:59
working on the formula
User 23/09/2018 - 04:59
ok
Excelchat Expert 23/09/2018 - 05:06
Can you check the formula in F
User 23/09/2018 - 05:08
it seems to work, but can you please add one more condition that I forgot to mention?
Excelchat Expert 23/09/2018 - 05:08
Let me know if you need any clarifications .. I used a if statement
Excelchat Expert 23/09/2018 - 05:08
Sure
Excelchat Expert 23/09/2018 - 05:08
What is it
User 23/09/2018 - 05:09
if the member's nickname is blank, it should be blank also
Excelchat Expert 23/09/2018 - 05:09
So if nickname is blank No need of any calculations right
User 23/09/2018 - 05:09
correct
Excelchat Expert 23/09/2018 - 05:10
can you check now
User 23/09/2018 - 05:11
yep works
Excelchat Expert 23/09/2018 - 05:11
:)
Excelchat Expert 23/09/2018 - 05:11
Do you have any queries relted tot he formula?
User 23/09/2018 - 05:12
??????????
Excelchat Expert 23/09/2018 - 05:12
Any questions or clarifications
Excelchat Expert 23/09/2018 - 05:12
:)
User 23/09/2018 - 05:12
no. Can I copy and paste the formula?
Excelchat Expert 23/09/2018 - 05:12
Yes
Excelchat Expert 23/09/2018 - 05:12
as long as the references to the 1st on are the same
Excelchat Expert 23/09/2018 - 05:13
So nick Name in Column D Guest 1 Name in G and so on
User 23/09/2018 - 05:13
I should have actually uploaded the actual file at the beginning
Excelchat Expert 23/09/2018 - 05:13
are the references different..
Excelchat Expert 23/09/2018 - 05:14
Excelchat Expert 23/09/2018 - 05:14
there is a paperclip symbol on the right
User 23/09/2018 - 05:14
ok standby
Excelchat Expert 23/09/2018 - 05:17
Just for your information this is the last extention so we have only 14 min left
User 23/09/2018 - 05:17
it says invalid extension
User 23/09/2018 - 05:18
it's an xlsm
Excelchat Expert 23/09/2018 - 05:18
Ys macro files arent allowed
Excelchat Expert 23/09/2018 - 05:18
Ok send me a screen shot of the excel so i can change the references
User 23/09/2018 - 05:19
one sec
User 23/09/2018 - 05:20
did it work?
Excelchat Expert 23/09/2018 - 05:20
yes
Excelchat Expert 23/09/2018 - 05:20
few min
Excelchat Expert 23/09/2018 - 05:21
the formula in the 1st cell G14 is
Excelchat Expert 23/09/2018 - 05:21
=IF(TODAY()<=\$B14,IF(AND(\$H14<>"",OR(\$I14="Show",\$I14="Confirmed")),1,0),IF(AND(\$H14<>"",\$I14="Show"),1,0))+IF(TODAY()<=\$B14,IF(AND(\$J14<>"",OR(\$K14="Show",\$K14="Confirmed")),1,0),IF(AND(\$J14<>"",\$K14="Show"),1,0))+IF(TODAY()<=\$B14,IF(AND(\$L14<>"",OR(\$M14="Show",\$M14="Confirmed")),1,0),IF(AND(\$L14<>"",\$M14="Show"),1,0))+IF(TODAY()<=\$B14,IF(AND(\$N14<>"",OR(\$O14="Show",\$O14="Confirmed")),1,0),IF(AND(\$N14<>"",\$O14="Show"),1,0))+IF(TODAY()<=\$B14,IF(AND(\$P14<>"",OR(\$Q14="Show",\$Q14="Confirmed")),1,0),IF(AND(\$P14<>"",\$Q14="Show"),1,0))
Excelchat Expert 23/09/2018 - 05:21
Then you can just drag it down
Excelchat Expert 23/09/2018 - 05:22
Can you verify it worked?
User 23/09/2018 - 05:23
ok got it
Excelchat Expert 23/09/2018 - 05:23
:)
Excelchat Expert 23/09/2018 - 05:24
Please do leave a feedback after you end the session ..
Excelchat Expert 23/09/2018 - 05:24
:)
Excelchat Expert 23/09/2018 - 05:24
Have a nice day
User 23/09/2018 - 05:24
thank you. So next time i need help there will be a charge?
Excelchat Expert 23/09/2018 - 05:24
Yse
Excelchat Expert 23/09/2018 - 05:24
Yes
User 23/09/2018 - 05:24
ok that's fair
Excelchat Expert 23/09/2018 - 05:24
:)
User 23/09/2018 - 05:24
bye

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.