Count with multiple criteria including a date plus an "or" variable

  • Hello all!


    I am stumped, I am a loss on how to make this calculation. The problem is my criteria is more complex for this calculation. There are a total of 5 criteria, one of which is a date, and then another that will need to use OR (at least I think). My more traditional formula is at the bottom, however it simply will not meet these needs. And with my Googling and research, I am stumped as to whether I should be using SUMPRODUCT, COUNTIFS or something else.
    Now I need something that will will accommodate the following


    [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Criteria #1

    [/td]


    [td]

    C2:C1000

    [/td]


    [td]

    Process 1

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Criteria #2

    [/td]


    [td]

    A2:A1000

    [/td]


    [td]

    Manager

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Criteria #3

    [/td]


    [td]

    E2:E1000

    [/td]


    [td]

    On or Before 3/1/2019

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Criteria #4

    [/td]


    [td]

    K2:K1000

    [/td]


    [td]

    Completed

    [/td]


    [td]

    OR

    [/td]


    [/tr]


    [tr]


    [td]

    Criteria #5

    [/td]


    [td]

    K2:K1000

    [/td]


    [td]

    In Progress

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]



    =SUMPRODUCT(--(C2:C10="Process 1"), --(A2:A10="Manager"))

  • Hello,


    For such a generic question ...


    The answer will also be rather generic ...


    With your Sumproduct() formula ...


    1. Using the * operator is equivalent to AND


    2. Using the + operator is equivalent to OR


    Should you need more concrete assistance, feel free to attach a sample file ( with 10 to 15 anonymized records )


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Glad you could fix your problem ... :wink:


    Thanks a lot for ... Both your Thanks ... AND for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!