Exact same formula different results.

  • Re: Exact same formula different results.


    Wow, NVBC, I have come up with another idea for this workbook that would be greatly beneficial. I'll happily create a new thread if you think it's necessary, as it's for a bit of a different purpose. It would be nice to index a list of the injuries from each company, or all companies.

  • Re: Exact same formula different results.


    Yes, post another thread... You can link to this one if relevant.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Exact same formula different results.


    Would somebody perhaps be able to help me organize this formula properly?



    This is the original:



    =IF($J$3="Overall",SUMPRODUCT(COUNTIFS(Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>","<"&$G$42),Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>",">="&$N$3))),COUNTIFS(Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>","<"&$G$42),Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>",">="&$N$3),Injuries!A2:A1000,$J$3))



    The new formula is trying to count Injuries!D2:D1000,{"LTI";"MA";"MD"} , but it's not returning the right number. For example,



    Here's how I modified the formula:



    =IF($J$3="Overall",SUMPRODUCT(COUNTIFS(Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>","<"&$G$42),Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>",">="&$N$3),Injuries!D2:D1000,{"LTI";"MA";"MD"})),COUNTIFS(Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>","<"&$G$42),Injuries!B2:B1000,IF($N$3="Entire Turnaround","<>",">="&$N$3),Injuries!A2:A1000,$J$3,Injuries!D2:D1000,{"LTI";"MA";"MD"}))



    The first formula is returning 2. The second formula should only return 1, but instead it's returning 0. Any thoughts if I'm phrasing that countif properly?

  • Re: Exact same formula different results.


    I placed your formula in the workbook you provided in your related thread here: http://www.ozgrid.com/forum/showthread.php?t=204286 which has the same tab names and references as your formula... and I got reasonable results...


    Are you sure the entries in column D don't have extra spaces or something?


    if yes, then post a new sample workbook showing how you arrive at the result you expect.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Exact same formula different results.


    Yeah it's really strange. It appears fine when the company is set to Overall, but when I choose "b", for example, the math doesn't add up.



    I think I must have my LTI, MA MD string in the wrong part of the formula.


    Also, I've got the results of those two formulas in U1 and V1


    U1 should return all injuries for the selected company/time frame


    V1 should return all MA, LTI, MD injuries for the selected company/time frame



    ozgrid.com/forum/core/index.php?attachment/72736/

    Files

    • sheet.xlsx

      (169.03 kB, downloaded 113 times, last: )
  • Re: Exact same formula different results.


    Yes, I didn't catch that part. Good job figuring it out!

    Where there is a will there are many ways. Finding one that works for you is the challenge!