Forum Discussion
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 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!
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.
10 Replies
Crimzo As a variant, a Pivot Table might do what you ask for, though in a slightly different format.
- 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!
Crimzo Like so?
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.
- 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.
