Forum Discussion
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...
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.
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.
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 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?
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
- Hi Hans Vogelaar, that works great, thank you so much!