**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

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

:)

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.*