I am trying to do Countifs with multiple criteria and an array

  • Hello Board,
    I have this formula..


    Code
    1. =20-COUNTIFS('Outlook Calendar'!$A:$A,"john smith*") + COUNTIFS('Outlook Calendar'!$A:$A,"ooo,pto") + COUNTIFS('Outlook Calendar'!$A:$A,"john smith*") + COUNTIFS('Outlook Calendar'!$A:$A,{"left;leaving;leave"})


    It counts the number of instances it finds john smith "AND" ooo "OR" pto. Each time it finds it, it Minus's 1 from 20. Then it counts how many times it finds "john smith" with either "left" OR "Leaving" OR "Leave". If it finds any, that all are True, then minus .5 (point 5).
    The wild card after john smith is because john smith is only the beginning of the sentence in that cell.
    The problem is it does not return the number i expect.
    If I just use the first part of the formula up to the "20" it works, but the second half does not find anything.
    Example would be....
    john smith "left" early today....result would be 19.5
    Example.... 'john smith" is "ooo".....result would be 19
    ect

  • Re: I am trying to do Countifs with multiple criteria and an array


    This has been solved with this....

    Code
    1. =20-COUNTIFS('Outlook Calendar'!$A:$A,"john smith*") + COUNTIFS('Outlook Calendar'!$A:$A,"ooo,pto") + SUM(COUNTIFS('Outlook Calendar'!$A:$A,"john smith*",'Outlook Calendar'!$A:$A,"*"&{"left";"leaving";"leave"}&"*"))/2