# Basic If Function problem

1. ## Basic If Function problem

Hi everyone,
This seems like a really elementary problem but bear with me. I have a list of employees and the dates they worked. I'm trying to formulate an IF function that will check the name of employees in the list against the to the contents of a particular cell and also check the date the employees worked against another cell that contains a specific date, and if both these conditions are met, mark that cell with an X, if not leave it empty. The formula I have right now is =IF(AND(A2:A27=G4, D2:D27,H3),"X",""). A2:A27 is the range that contains employee names, G4 is the first employee's name who I want to check if he is on the list, D2:D27 is the list of days they worked on and H3 is the date I want to know if this particular employee worked in. Using this formula returns either an empty cell of a #NAME? error. Is this a cell formatting issue? Please help!
Thank you.

2. ## Re: Basic If Function problem

Use

=IF(COUNTIFS(A2:A27,G4,D2:D27,H3),"X","")

3. ## Re: Basic If Function problem

Thanks for the quick response, Ace_XL but that returns empty cells even in cases where I know there should be an X. What am I doing wrong?

4. ## Re: Basic If Function problem

Try

=IF(CONCATENATE(A2,D2)=CONCATENATE(\$G\$4,\$H\$3),"X","")

Drag this formula down each cell of the column you want the "X" in.
For instance, start in cell K2, if you want the "X" in the K column, and 'Fill Down' to cell K27. The "A2, D2" should change to the corresponding, respective rows while the \$G\$4,\$H\$3 stays the same.

5. ## Re: Basic If Function problem

Thanks, Old4xford but this didn't work either.
I changed the absolute cell references to =IF(CONCATENATE(\$A\$2,\$D\$2)=CONCATENATE(\$G4,\$H\$3),"X","")
But even when I tried it without those changes I still get blank cells.
Maybe I should upload part of my workbook here? How do I do that?
Thanks

6. ## Re: Basic If Function problem

I'm thinking of the 'sumproduct' formula or a pivot table.
Please post an excel file to test it.

7. ## Re: Basic If Function problem

I've attached my data. ignore the weird names, I changed the actual names to protect their privacy.
Thanks for all your help everyone.

8. ## Re: Basic If Function problem

Take a look at this pivot table

9. ## Re: Basic If Function problem

Wow this is exactly what I wanted! Thanks, Popipipo. How did you do it?

10. ## Re: Basic If Function problem

Use F1 or/and google for more explanation.
They can better explane then I can.

11. ## Re: Basic If Function problem

Okay I used a pivot table to get the info that I wanted. Popipipo, I just want to confirm if I matched the field names to the right data tables. I put "First Name" under Report Filter, "Date of Activity" under Column Labels, "Last Name" under Row Labels and "Organisation" under Values.
Additionally, is it possible to create a table that shows a list of all calendar days in a month and have the days they worked marked in some way. That way, I could easily see what days the employees *didn't* work which is just as important as the days they did.
Thanks in advance.

12. ## Re: Basic If Function problem

Try attachment.

13. ## Re: Basic If Function problem

Bebo021999 this is precisely what I needed. How did you do it?
Thanks a lot.

14. ## Re: Basic If Function problem

Do I need to submit another question to ask how you get the last part?
Thanks.

15. ## Re: Basic If Function problem

Originally Posted by MelodyK
How did you do it?
MelodyK, SUMPRODUCT is common function with this such task. I think searchingSUMPRODUCT in this forum give you better guidance than me since my English is not well.
This is one of a link about how to use SUMPRODUCT.

16. ## Re: Basic If Function problem

Thanks, Bebo021999. I tried the formula =SUMPRODUCT(--(A:A=G4),--(D:D=H3)) where the column A contains all the employee's last name and G4 contains the name of the particular employee I'm looking for and column D contains the date they worked on and H3 the particular date I want to know if they worked on. Unfortunately this returned all 0s. Bebo021999 can you paste your formula here so I can adopt it as necessary? Thank you.

17. ## Re: Basic If Function problem

Never refer to whole range, i.e A:A, it may causes speed slow down.
Anyway try
=SUMPRODUCT((A1:A100=G4)*(--D1:D100=H3))
I add--to convert date stored as date (maybe any) into number
Does it work?
if not,
* Check for space(s) in G4
* try to post your sample.

18. ## Re: Basic If Function problem

OMG it works. Thanks, Bebo021999! I would add your reputation again if I could but I have to give reputation to other people before I can come back to you.
I have one question though. If someone has 2 entries in the same day, the sumproduct is going to give 2 and not 1, right?
Don't worry about your English, it's my second language too.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1