Forum Discussion

Crimzo's avatar
Crimzo
Copper Contributor
Nov 03, 2020
Solved

Auto populate a table using two filtered ranges from another table

Hi, sorry in advance if this has already been posted.   I was wondering if there is a way to autopopulate a table using a separate table total with a custom filtered range on two columns.   In th...
excel
Formulas and Functions
training
  • HansVogelaar's avatar
    HansVogelaar
    Nov 03, 2020

    Crimzo 

    How about

    =COUNTIFS('Table A'!D$12:D$22,"<>",'Table A'!$A$12:$A$22,OFFSET($A3,-MOD(ROW()+1,4),0),'Table A'!$C$12:$C$22,$B3)

    See the attached version.

HansVogelaar's avatar
HansVogelaar
MVP
Nov 03, 2020

Crimzo 

I don't really understand your question, so this may be off. In the attached version, I entered a formula in C3, then used Paste Formulas to paste it to the other cells.

Crimzo's avatar
Crimzo
Copper Contributor
Nov 03, 2020
Hi Hans Vogelaar, thank you for your response! Sorry the question is poorly worded, I think I am struggling to get the right words. The formula works great, but is there a way to put the Total value (I meant to put SUM rather than SUBTOTAL) that is in the grey highlights in table A into the Table B? i.e. the total for Unit 1 at 1 m is 3 for Data 1, Data 2 and Data 3. Then for Unit 1 at 2m the total is 1 for Data 1, and 2 for Data 2 and 3.
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 03, 2020

    Crimzo 

    How about

    =COUNTIFS('Table A'!D$12:D$22,"<>",'Table A'!$A$12:$A$22,OFFSET($A3,-MOD(ROW()+1,4),0),'Table A'!$C$12:$C$22,$B3)

    See the attached version.

    • Crimzo's avatar
      Crimzo
      Copper Contributor
      Nov 03, 2020

      HansVogelaar Hi Hans Vogelaar, the formula you gave above rightly calculates the Countif per cell, is there a way to link that to the grey box and use the value in there? I ask this, as I would like to do the same for the Lower and Upper Bounds rather than total of the data. Would I replicate your formula and add the calculation steps at the start? 

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Nov 03, 2020

        Crimzo 

        In D8: =COUNT(D$16:D$26)

        In D9: =AVERAGE(D$16:D$26)

        In D10: =STDEV(D$16:D$26)

         

        Fill right to column F

    • Crimzo's avatar
      Crimzo
      Copper Contributor
      Nov 03, 2020
      Hi Hans Vogelaar, that works great, thank you so much!

Resources