Expert Solution - Excel COUNT Problems

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

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

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

Excelchat Expert 23/09/2018 - 04:49

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

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

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

If you can then please upload the file

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?

[Uploaded an Excel file]

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