Nov 03 2020 06:55 AM - edited Nov 03 2020 09:31 AM
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 the example attached, I would like to auto populate Table A with the Totals (grey highlighted) for Data 1,2,3 at 1m,2m,3m depth intervals and for units 1 and 2 using all samples from Table A. i.e. for unit 1 at 1 m the total is 3, unit 2 at 1m is 3. or unit 2 at 2m has a total of 2. Or with example 2, can the Lower and Upper bounds be stated in the same way?
Any help will be much appreciated!
Nov 03 2020 07:23 AM
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.
Nov 03 2020 07:42 AM
@Crimzo As a variant, a Pivot Table might do what you ask for, though in a slightly different format.
Nov 03 2020 07:52 AM
Nov 03 2020 07:56 AM
Nov 03 2020 08:03 AM
SolutionHow 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.
Nov 03 2020 08:04 AM
@Crimzo Like so?
Nov 03 2020 08:09 AM
Nov 03 2020 08:11 AM
Nov 03 2020 09:14 AM
@Hans Vogelaar 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?
Nov 03 2020 11:49 AM
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
Nov 03 2020 08:03 AM
SolutionHow 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.