+ Reply to Thread
Results 1 to 18 of 18

Basic If Function problem

  1. #1
    Registered User
    Join Date
    04-22-2014
    Location
    Addis Abeba, Ethiopia
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Basic If Function problem

    Use

    =IF(COUNTIFS(A2:A27,G4,D2:D27,H3),"X","")
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    04-22-2014
    Location
    Addis Abeba, Ethiopia
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #4
    Registered User
    Join Date
    03-12-2014
    Location
    Central Texas
    MS-Off Ver
    Excel 2010
    Posts
    55

    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.
    Last edited by Old4xford; 04-22-2014 at 02:32 PM. Reason: Typo correction; expanded explanation.
    Oh! I get it. I need rep--->҉

  5. #5
    Registered User
    Join Date
    04-22-2014
    Location
    Addis Abeba, Ethiopia
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Basic If Function problem

    I'm thinking of the 'sumproduct' formula or a pivot table.
    Please post an excel file to test it.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  7. #7
    Registered User
    Join Date
    04-22-2014
    Location
    Addis Abeba, Ethiopia
    MS-Off Ver
    Excel 2010
    Posts
    10

    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.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Basic If Function problem

    Take a look at this pivot table
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-22-2014
    Location
    Addis Abeba, Ethiopia
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Basic If Function problem

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

  10. #10
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Basic If Function problem

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

  11. #11
    Registered User
    Join Date
    04-22-2014
    Location
    Addis Abeba, Ethiopia
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Basic If Function problem

    Try attachment.
    Attached Files Attached Files
    Quang PT

  13. #13
    Registered User
    Join Date
    04-22-2014
    Location
    Addis Abeba, Ethiopia
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Basic If Function problem

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

  14. #14
    Registered User
    Join Date
    04-22-2014
    Location
    Addis Abeba, Ethiopia
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Basic If Function problem

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

  15. #15
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Basic If Function problem

    Quote Originally Posted by MelodyK View Post
    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. #16
    Registered User
    Join Date
    04-22-2014
    Location
    Addis Abeba, Ethiopia
    MS-Off Ver
    Excel 2010
    Posts
    10

    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. #17
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    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. #18
    Registered User
    Join Date
    04-22-2014
    Location
    Addis Abeba, Ethiopia
    MS-Off Ver
    Excel 2010
    Posts
    10

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Basic IF problem!
    By [email protected] in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-13-2012, 12:39 PM
  2. Another basic problem
    By Niddeh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2006, 02:39 PM
  3. Basic Compile Problem
    By mpeplow in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2006, 03:55 PM
  4. Yet another basic problem
    By Vepa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-09-2006, 10:30 AM
  5. Basic problem
    By melvin in forum Excel General
    Replies: 0
    Last Post: 02-02-2005, 05:19 AM

Tags for this Thread

Bookmarks

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