Forum Discussion

FrameofLightDesigner's avatar
FrameofLightDesigner
Copper Contributor
Jul 21, 2020

Structured References Issue

I'm having an issue with my structured reference formula. It is part of a workbook for my job. And on one worksheet, I have a table. As folks check in, I have a timestamp put on it. in a column, outs...
excel
Formulas and Functions
office 365
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 22, 2020

FrameofLightDesigner 

Thank you. An error is since in formula

=SUM(COUNTIFS('Rooming List'!H2:H252,"<=14",RoomingList[BRANCH],{"DAR/APP","DAR/ENL","DAV/APP","DAV/ENL"}))

ranges 'Rooming List'!H2:H252 and RoomingList[BRANCH] are of different size. Formula requires the same size of the ranges. You may modify it as

=SUM(COUNTIFS($H$2:INDEX(H:H,ROWS(RoomingList)+1),"<=14",RoomingList[BRANCH],{"DAG/APP","DAG/ENL"}))

(see in cell B24), when it works. At least in this part.

  • FrameofLightDesigner's avatar
    FrameofLightDesigner
    Copper Contributor
    Jul 22, 2020

    SergeiBaklan  That worked beautifully! I can't thank you enough! I've been trying to fix that for 5 days now! You're amazing!

    • FrameofLightDesigner's avatar
      FrameofLightDesigner
      Copper Contributor
      Jul 22, 2020

      SergeiBaklan I spoke to quickly. It's not working in every cell. It worked in B24 and E24, but not C24 and J24, so far. What could be the cause?

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 22, 2020

        FrameofLightDesigner 

        Sorry, I also was too fast, forgot to add sheet name copy/pasting the formula. It shall be

        =SUM(COUNTIFS('Rooming List'!$H$2:INDEX('Rooming List'!$H:$H,ROWS(RoomingList)+1),"<=14",RoomingList[BRANCH],{"DAG/APP","DAG/ENL"}))

        Corrected for entire row 24.

        Without data errors are not always visible.

Resources