SOLVED

# Auto populate a table using two filtered ranges from another table

Occasional Contributor

# Auto populate a table using two filtered ranges from another table

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!

10 Replies

# Re: Auto populate a table using two filtered ranges from another table

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.

# Re: Auto populate a table using two filtered ranges from another table

@Crimzo As a variant, a Pivot Table might do what you ask for, though in a slightly different format.

# Re: Auto populate a table using two filtered ranges from another table

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.

# Re: Auto populate a table using two filtered ranges from another table

Hi Riny_van_Eekelen, thank you for your reply and suggestion, I haven't used Pivot Tables but will give it a look. I poorly worded by above question and meant to ask if the grey highlighted totals can be put into the information rather than the raw data per depth and unit. i.e. Unit 1 at 1 m has a total data 1 value of 3. Will see if I can do this with the pivot plot!
best response confirmed by Crimzo (Occasional Contributor)
Solution

# Re: Auto populate a table using two filtered ranges from another table

=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 Like so?

# Re: Auto populate a table using two filtered ranges from another table

Hi Hans Vogelaar, that works great, thank you so much!

# Re: Auto populate a table using two filtered ranges from another table

Hi Riny_van_Eekelen, this works really well thank you! Will definitely start using pivot tables!

# Re: Auto populate a table using two filtered ranges from another table

@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?

# Re: Auto populate a table using two filtered ranges from another table

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