Exact same formula different results.

  • I have a drop-down in J3. The selections are Overall, A, B, C, D, E


    If I choose Overall, I want to search for a date (N3) from all the companies in my Named Range "Companies". The problem is, this formula is not working properly.


    If I use an array, it sort of works, but when I apply to other cells and change the final reference at the end to match, from V4 to say, V10, I get #VALUE error.



    =SUMPRODUCT(COUNTIFS(IF($J$3="Overall",INDIRECT("'"&Companies&"'!A:A"),INDIRECT("'"&$J$3&"'!A:A")),"<"&$G$42,IF($J$3="Overall",INDIRECT("'"&Companies&"'!A:A"),INDIRECT("'"&$J$3&"'!A:A")),">="&$N$3,IF($J$3="Overall",INDIRECT("'"&Companies&"'!D:D"),INDIRECT("'"&$J$3&"'!D:D")),$V4))

  • Re: Exact same formula different results.


    Just a guess... does this work?


    [COLOR="#0000FF"]=SUMPRODUCT(--(($J$3="Overall")+((INDIRECT("'"&Companies&"'!A:A")<$G$42)*(INDIRECT("'"&Companies&"'!A:A")>=$N$3)*(INDIRECT("'"&Companies&"'!D:D")=$V4))))[/COLOR]


    If not, please post a sample workbook with expected results.

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


    MS Excel MVP 2010-2016

  • Re: Exact same formula different results.


    Hey NBVC, thanks for the effort, I'm afraid it didn't quite work. Here's my workbook.


    Basically, if you record an injury in the upper left box, you should see it tally on the right side according to body part. Then you can filter with the two grey boxes, currently set on "Overall" and "8/27/2017". The date box represents the beginning of that particular week., so, if your injury falls after that date and before the beginning of the following week, it should appear on the right.


    The formula I shared belongs beside "Eyes", and below. (W4:W27, essentially)


    Currently, it works with Eyes, but not the others. I tried using a helper cell, so "Overall" = 1 (C42), but that didn't work. "Companies" is a variable named range.


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

  • Re: Exact same formula different results.


    Try:


    [COLOR="#0000FF"]=IF($J$3="Overall",SUMPRODUCT(COUNTIFS(INDIRECT("'"&Companies&"'!A:A"),"<"&$G$42,INDIRECT("'"&Companies&"'!A:A"),">="&$N$3,INDIRECT("'"&Companies&"'!D:D"),$V4)),COUNTIFS(INDIRECT("'"&$J$3&"'!A:A"),"<"&$G$42,INDIRECT("'"&$J$3&"'!A:A"),">="&$N$3,INDIRECT("'"&$J$3&"'!D:D"),$V4))[/COLOR]


    * there is no need to confirm with CTRL+SHIFT+ENTER... just ENTER will suffice.

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


    MS Excel MVP 2010-2016

  • Re: Exact same formula different results.


    ! Perfect! Can you explain to me what (or why) you changed what you did? Looks like I had some things in the wrong order.


    I'd like to add the date field as an additional variable, meaning, if they select "Entire Turnaround" it will search all dates.

  • Re: Exact same formula different results.


    I split your original formula into one only IF() formula which will perform the "Overall" count if Overall is chosen or it will perform specific company count if a specific company was chosen. With the way you had it, the SUMPRODUCT(COUNTIFS()) wasn't creating the array of companies from INDIRECT("'"&Companies&"'!A:A") that you needed to check when "Overall" was selected.

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


    MS Excel MVP 2010-2016

  • Re: Exact same formula different results.


    Since it is the Criteria itself, and not the criteria ranges, that you want to affect with the date dropdown, then we can use the nested IF's...


    Try:
    [COLOR="#0000FF"]
    =IF($J$3="Overall",SUMPRODUCT(COUNTIFS(INDIRECT("'"&Companies&"'!A:A"),IF($N$3="Entire Turnaround","<>","<"&$G$42),INDIRECT("'"&Companies&"'!A:A"),IF($N$3="Entire Turnaround","<>",">="&$N$3),INDIRECT("'"&Companies&"'!D:D"),$V4)),COUNTIFS(INDIRECT("'"&$J$3&"'!A:A"),IF($N$3="Entire Turnaround","<>","<"&$G$42),INDIRECT("'"&$J$3&"'!A:A"),IF($N$3="Entire Turnaround","<>",">="&$N$3),INDIRECT("'"&$J$3&"'!D:D"),$V4))[/COLOR]


    The "<>" means count all non blanks.

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


    MS Excel MVP 2010-2016

  • Re: Exact same formula different results.


    Are you sure?.. works for me.... when I pick that combination on the sample workbook you posted, I get "2" for "Eyes"... because in sheet "a" there are 2 entries for "Eyes".

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


    MS Excel MVP 2010-2016

  • Re: Exact same formula different results.


    You may have copied my formula too quickly... I did a quick update to it right after I posted it originally, and perhaps you grabbed it too quickly.


    Try copying my last formula again and trying it.

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


    MS Excel MVP 2010-2016

  • Re: Exact same formula different results.


    Hey again, so I have another formula I'd like to ask you about. I'd post a new thread, but since the file's already here it made sense to ask. I've just spent a couple hours trying to rebuild the formula to work but I feel like you'd be able to parse it out much easier.



    On the spreadsheet I uploaded, if you look over in Main! BA2 & BB2 and BA10 & BB10, you'll see something called RIF and PIF. These are found in each of the companies pages in column P and Q (Weekly RIF and PIF). I'd like a formula to be able to view by Overall + Entire Turnaround, or simply any company and from any week. Does that make sense?


    At first I tried editing the other formula, but I found a COUNTIF is not what I was looking for. More like a sumproduct of VLOOKUPS right?

  • Re: Exact same formula different results.


    You just need to change the COUNTIFS to SUMIFS and add a summing range, plus change the date ranges to column F of the company sheets...


    so in BB2:


    [COLOR="#0000FF"]=IF($J$3="Overall",SUMPRODUCT(SUMIFS(INDIRECT("'"&Companies&"'!P:P"),INDIRECT("'"&Companies&"'!F:F"),IF($N$3="Entire Turnaround","<>","<"&$G$42),INDIRECT("'"&Companies&"'!F:F"),IF($N$3="Entire Turnaround","<>",">="&$N$3))),COUNTIFS(INDIRECT("'"&$J$3&"'!F:F"),IF($N$3="Entire Turnaround","<>","<"&$G$42),INDIRECT("'"&$J$3&"'!F:F"),IF($N$3="Entire Turnaround","<>",">="&$N$3)))[/COLOR]


    and similarly of BB10, subbing the summing range to reference column Q

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


    MS Excel MVP 2010-2016

  • Re: Exact same formula different results.


    Hi NBVC, sorry it's taken so long to get back to you, I've just been caught up with other things at work. Regarding the most recent issue in the thread, I'm attempting to create a variable calculator for viewing recordable injury frequency, based on a formula from OSHA.


    in the text.xlsm sample, as you know, the variables are "company" in J3 and "date range" in N3.


    The formula for calculating a recordable injury frequency, according to OSHA is this:


    (# of injuries x 200,000 ) / (total workforce hours)


    For example, 3 injuries in 325,000 hours would equal a RIF of 1.85.


    So, in the sample, when I select a company, and a range, I would like to be able to see the RIF for any particular week/company combination.
    Likewise, if Overall is selected, then it should be all companies in any given date range, etc.


    The formula you provided above:


    =IF($J$3="Overall",SUMPRODUCT(SUMIFS(INDIRECT("'"&Companies&"'!P:P"),INDIRECT("'"&Companies&"'!F:F"),IF($N$3="Entire Turnaround","<>","<"&$G$42),INDIRECT("'"&Companies&"'!F:F"),IF($N$3="Entire Turnaround","<>",">="&$N$3))),COUNTIFS(INDIRECT("'"&$J$3&"'!F:F"),IF($N$3="Entire Turnaround","<>","<"&$G$42),INDIRECT("'"&$J$3&"'!F:F"),IF($N$3="Entire Turnaround","<>",">="&$N$3)))


    was returning numbers, but they weren't quite what I was looking for. I was expecting it would return the hours from column P of my company's Sheet, at which point I would also repurpose the formula to gather the number of injuries from column H (of the company's page), and then use OSHA's formula to finish the job.


    Is this making any sense? It sounds super confusing just writing it.

  • Re: Exact same formula different results.


    Looks like I didn't change the second part of the formula from Countifs to Sumifs...


    [COLOR="#0000FF"]=IF($J$3="Overall",SUMPRODUCT(SUMIFS(INDIRECT("'"&Companies&"'!P:P"),INDIRECT("'"&Companies&"'!F:F"),IF($N$3="Entire Turnaround","<>","<"&$G$42),INDIRECT("'"&Companies&"'!F:F"),IF($N$3="Entire Turnaround","<>",">="&$N$3))),[COLOR="#FF0000"]SUMIFS[/COLOR](INDIRECT("'"&Companies&"'!P:P"),INDIRECT("'"&$J$3&"'!F:F"),IF($N$3="Entire Turnaround","<>","<"&$G$42),INDIRECT("'"&$J$3&"'!F:F"),IF($N$3="Entire Turnaround","<>",">="&$N$3)))[/COLOR]


    This should give total hours from column P of your company sheets.

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


    MS Excel MVP 2010-2016

  • Re: Exact same formula different results.


    Sorry...


    try:


    [COLOR="#0000FF"]=IF($J$3="Overall",SUMPRODUCT(SUMIFS(INDIRECT("'"&Companies&"'!P:P"),INDIRECT("'"&Companies&"'!F:F"),IF($N$3="Entire Turnaround","<>","<"&$G$42),INDIRECT("'"&Companies&"'!F:F"),IF($N$3="Entire Turnaround","<>",">="&$N$3))),SUMIFS(INDIRECT("'"&$J$3&"'!P:P"),INDIRECT("'"&$J$3&"'!F:F"),IF($N$3="Entire Turnaround","<>","<"&$G$42),INDIRECT("'"&$J$3&"'!F:F"),IF($N$3="Entire Turnaround","<>",">="&$N$3)))[/COLOR]

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


    MS Excel MVP 2010-2016

  • Re: Exact same formula different results.


    There we go! I was trying to wrap my head around it, and started wondering how we were specifying the company page. I think that will do it. I'll let you know if something comes up.


    Thanks so much, again.