COUNTIFS with dynamic named ranges

    I have the following named ranges on a worksheet (KPI_1): KPI_1 (this has the Pass/Fail result), KPI_1_MTH_YR (this has the month and year), KPI_1_QUARTER (Determines the quarter - period being reported) using fixed ranges for each of the above KPI_1 = KPI_1!$N$5:$N$20000, KPI_1_MTH_YR = KPI_1!$B$5:$B$20000, KPI_1_QUARTER = KPI_1!$A$5:$A$20000
    The data is entered from row 5 onwards, as the top 4 rows have been used for the column titles. Using the following formula with the defined named ranges (i.e. $A$5:$A$20000) work fine:
    =COUNTIFS(KPI_1_QUARTER,$I$1,KPI_1_MTH_YR,I2,KPI_1,"Pass")/COUNTIFS(KPI_1_QUARTER,$I$1,KPI_1_MTH_YR,I2)),"NDA",COUNTIFS(KPI_1_QUARTER,$I$1,KPI_1_MTH_YR,I2,KPI_1,"Pass")/COUNTIFS
    (KPI_1_QUARTER,$I$1,KPI_1_MTH_YR,I2))
    returning a decimal value or percentage.



    However, when using the following to create a dynamic named range KPI_1 = OFFSET(KPI_1!$N$5, 0, 0, COUNTA(KPI_1!$N:$N), 1) using similar for the KPI_1_MTH_YR and KPI_1_QUARTER formula returns error #VALUE.



    Any ideas why the dynamic named ranges are not working?


    Please note this question has been posted on Stackoverflow: https://stackoverflow.com/ques…with-dynamic-named-ranges



    [h=1][/h]

    Re: COUNTIFS with dynamic named ranges


    Do all your columns that you are using for your named ranges have the same COUNTA() result? (i.e. each dynamic named range must result in same number of rows for the COUNTIFS to work).


    If they are not the same, then consider using one specific column for all your COUNTA() parameters in the DNR's. Make sure it is is a column that has no blanks rows interspersed within it.

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

    Re: COUNTIFS with dynamic named ranges


    Hi NBVC,


    Yes the columns I am referring to all have the same COUNTA() result. This was the first thing that I checked before using the COUNTIFS statement.

    Re: COUNTIFS with dynamic named ranges


    Are you able to post the workbook (with no confidential data)?

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

    Re: COUNTIFS with dynamic named ranges


    Hi NVBC,


    forum.ozgrid.com/index.php?attachment/73094/



    Please find the file attached. As the individual KPIs are dependent on dates there are separate spreadsheets which record the data. These are then referenced on the monthly summary, where the COUNTIFS statements are used to determine the percentages passing and indicated on the monthly summary sheet.


    Thanks for looking into this, appreciated.


    Baber62

    Re: COUNTIFS with dynamic named ranges


    To me it seems your dynamic named ranges are evaluating properly. I think it is that there are no matches in the KP_5 sheet that meet the criteria.


    Note that you have specific dates in Row 2 of the Monthly Summary sheet, but in column B of KP_5 you seem to be always resulting with the first day of the month in column O.... you won't have matches unless row 2 of the Monthly summary sheet result in the 1st of the month.

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

    Re: COUNTIFS with dynamic named ranges


    forum.ozgrid.com/index.php?attachment/73095/


    Note that only KPI_1 is the only one which has a dynamic named range the ranges for the all the remaining sheets are fixed named ranges.


    The reason for column 2 to have the first of the month is so that the we can determine the quarter and match this against the month in the summary sheet in row 2. The reason is that the KPIs are reported on a monthly basis. However, see attached sheet which has the correct results that should be reported with the fixed named ranges.

    Re: COUNTIFS with dynamic named ranges


    Where exactly am i looking?

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

    Re: COUNTIFS with dynamic named ranges


    Just the KPI_1 spreadsheet at the moment in the workbook ...tested2, the named ranges are fixed lengths i.e. $A$5:$A$20000


    in the workbook ...tested3, the named ranges are dynamic i.e. $A:$A. It's there that problem occurs the dynamic named ranges are not working.

    Re: COUNTIFS with dynamic named ranges


    To me both workbooks you sent look the same, except you have more info the KP_1 sheet.


    I am interested in the formula you say is not working... I assume that is in the Monthly summary sheet, in the even row numbers after row 4?


    in your last upload they all show "NDA" which is because the #DIV/0 error you are getting with the dividing of the COUNTIFS() functions. The Countifs are resulting in 0 because it is not finding exact matches.


    Can you show where the formula is giving the result you are expecting in this last workbook and where the same result in the original workbook is not the same?

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

    Re: COUNTIFS with dynamic named ranges


    forum.ozgrid.com/index.php?attachment/73101/


    Hi NVBC,


    Please find attached the zip file contain both files sent earlier. I have copied the first part of the formula into one cell into cell A31 on both sheets. Formula is =COUNTIFS(KPI_1_QUARTER,$I$1,KPI_1_MTH_YR,I2,KPI_1,"Pass") which shows that 2 KPI passes in April 2017 which is correct in the workbook ...tested2. Look at the filtered values on the KPI_1 sheet.


    However, after creating dynamic named ranged in ...tested3 and using the same formula but the result is giving me #VALUE. Check the KPI_1 sheet filtered in ..tested3 and it shows 2 which should be the same result as shown in ...tested2.


    That's the bit where it doesn't seem to work.

    Re: COUNTIFS with dynamic named ranges


    The very first thing I mentioned was to make sure that each of the dynamic named ranges evaluated to the same number of rows.


    When I evaluate the one you put in A31, it get the attached result... which clearly resolves into 3 differently sized ranges....[ATTACH=CONFIG]73118[/ATTACH]


    As I had mentioned before, in this situation, use the same COUNT() range in all of your KP1 dynamic named ranges, so that you are sure of the row count.

    Re: COUNTIFS with dynamic named ranges


    forum.ozgrid.com/index.php?attachment/73129/


    Have also posted this question on Experts Exchange at the following location: https://www.experts-exchange.c…med-ranges.html#a42216526


    [FONT=&amp]Have sorted it out almost ... Cleared all the debris left over from the original sheet there was extra data below the range that I was looking in and this was causing the erroneous COUNTAs. However, now that it has all been cleared up I am still get problem with KPI_3. I have posted the sheet rev 4 when I break down the COUNTIFS into parts I am getting the answer I expect however, when I combine the three COUNTIFS it is giving me an error #VALUE. Any ideas as to why this should be happening?[/FONT]

    Re: COUNTIFS with dynamic named ranges


    Have found the solution ... people had made entries lower down on the sheet. These were in the columns which were being used in the countifs. Hence the Countifs were not working after thoroughly going through the sheet have resolved alll issues and managed to get the countifs working on dynamic named ranges. Thanks to all those who have contributed.

Participate now!

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

Register Yourself Login