sum a table based on another filtered table

[CJ_London]

Hi,

I'm trying to total a column in a table (table2) based on the filtered result of another table (table1). I thought this would be straightforward using dsum or sumif but both sum on the basis of an unfiltered table

these are my test tables

Capture.JPG

If table1 is filtered to select M gender than the value I need is 90 - sum of A and D's value1's in table 2
If I filtered on M Gender and 1 Month then the value would be 78 - just sum of A's value1's in table2

Is there a way for dsum or sumif (or another function) to recognise the filtered list?

Many thanks in advance

[rcm]

Could you send the sample in excel format?

a crude way to make the function =sumif() work would be to include the gender and month in table2 (it can be done automatically via formulas, at least the gender part)

[CJ_London]

tablesumtest.xlsxThanks - please find attached

Including the gender and month in table2 won't work in this instance (well, it would but would be horribly cumbersome) - it is part of an automated report generation and in the 'real' system there are around 250 columns in Table1 that could be filtered and several table2's that are referenced - and each of those have 20 or 30 columns to be summed. The values actually appear at the bottom of Table1.

In a database it would not be an issue, but my client insists on using Excel.

[rcm]

try the macro by pressing the button after defining the filter criteria

[CJ_London]

Hi rcm

Thanks for the code - I had considered something similar - basically using the xlCellTypeVisible property but was hoping there was an excel function which would do the job. Looks like I'll need to go the code route.

[FDibbins]

You dont really show all the sample data, but 2 2 ways come to mind....
1. using SUBTOTAL(109,range)
2. using sumifS() to include only M or only F

[CJ_London]

thank you for your help. I had to create a function, but the attached does exactly what I require.

Filter table1 as you like and the values recalculate as they should