Page 1 of 5 1 2 3 ... LastLast
Results 1 to 10 of 45

Thread: Do I use a COUNTIF or a IFERROR(INDEX(MATCH formula??

  1. #1

    Exclamation Do I use a COUNTIF or a IFERROR(INDEX(MATCH formula??



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello Excel Experts!

    I need to get the Event Name to show in the weekly tab during the time of the event along with, if possible, the start and end times.

    I currently have a COUNTIF in there for the times, but I do not know how to let it give the Event Name from the Event List. This is what I currently have:

    =COUNTIFS(CSEvents[Date of Event], $C20, CSEvents[Start Time], "<="&N$6, CSEvents[End Time],">="&N$6,CSEvents[Calendar Entry],INDEX($M$2:$M$5,MOD(ROWS($1:19),6)))*N$6

    I have also played around with the formula below, but it does not give the start and end times, nor the name:

    {=IFERROR(INDEX(CSEvents,MATCH(TEXT($C41+O$6,"mmddyyyhh")&$M$5,TEXT(CSEvents[[Start]:[Start]],"mmddyyyhh")&CSEvents[[*Kind of Event]:[*Kind of Event]],0),2),IFERROR(INDEX(CSEvents,MATCH(TEXT($C41+O$6,"mmddyyyhh")&$M$5,TEXT(CSEvents[[Finish]:[Finish]],"mmddyyyhh")&CSEvents[[*Kind of Event]:[*Kind of Event]],0),3),""))}

    Suggestions?

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Can you post a sample workbook showing examples of expected results?

    Also, if you have posted the question in other forums, please advise and show links to those threads here.


  3. #3
    Hi,

    It wont let me share the link so I have attached the file.


    Thank you for your time!
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    What forum(s) did you post at?


  5. #5
    I have tried excelforum.com and mrexcelforum.com and nobody has responded yet. I posed on 8/14 to both. Thought I would try here too so see if anyone here could help. Ealier this month I have also tired microsoft community, but nobdy there knew how to do it. Can it be done??

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    The result of the COUNTIFS() portion seems to always be 1. Is that true? Meaning there is always only one row that matches all the criteria in the CS Events List sheet?


  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    If so, try for example to get the Event for the result in E10 of Weekly Calendar:

    =IFERROR(INDEX(CSEvents[Event Name],MATCH(1,INDEX((CSEvents[Date of Event]= $C10)*(CSEvents[Start Time]<=O$6)*(CSEvents[End Time]>=O$6)*(CSEvents[Calendar Entry]=INDEX($M$2:$M$5,MOD(ROWS($1:9),6))),0),0)),"")

    P.S. Looks like this are your links: http://www.excelforum.com/excel-formulas-and-functions/1031557-can-you-help-need-to-add-to-an-array-formula.html
    http://www.mrexcel.com/forum/excel-q...-formulas.html


    Last edited by NBVC; 2014-08-19 at 05:51 PM.


  8. #8
    Thank you! But when I enter that into a cell and copy it over to the row it shows the begining of the event name(more than what I could do!) but then the color for the event category and the time are gone.

  9. #9
    I also need to add rows to each day so that there can be up to 7+ events per day to see how they are laid out by hr on the weekly. but when I copy and past the formula down to the added rows, the new events do not show. Can you help with this as well? Sorry, and thank you!


    *For the previous message, would I just need to add the conditional formatting again for the color categories to show?
    Last edited by Turtle; 2014-08-19 at 06:26 PM.

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,491
    Articles
    0
    Excel Version
    Excel 2016
    Quote Originally Posted by Turtle View Post
    *For the previous message, would I just need to add the conditional formatting again for the color categories to show?
    probably....

    Try posting back the workbook showing samples of expected results, and if not obvious, how they were achieved.


Page 1 of 5 1 2 3 ... LastLast

Posting Permissions

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