Auto populate a table using two filtered ranges from another table

[Crimzo]

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! 

[#2]

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

[#3]

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

[#4]

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.

[#5]

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!

[#6 · Accepted solution]

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

[#7]

@Crimzo Like so?

[#8]

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

[#9]

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

[#10]

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

[#11]

@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