Subtotal and Sumif problem.

[Tipler93]

Hey Everyone,

I have an Excel document with two sheets(tabs).

One is called "AnalyseData" and the other is called "New Table".

I want to get the total of values in column K in "AnalyseData" WHEN:

Column F in AnalyseData = Cell D12 in New Table
Column D in AnalyseData = Cell B2 in New Table
Column D in AnalyseData = Cell B3 in New Table

This is my current formula:

=SUMIFS(AnalyseData!$K:$K,AnalyseData!$F:$F,'New Table'!$D$12,AnalyseData!$D:$D,'New Table'!$B$2,AnalyseData!$D:$D,'New Table'!$B$3)


Now I want to be able to filter the raw data in AnalyseData and make it so the formula only sums up the visible cells in column K. I have tried to use Subtotal and Sumproduct but I always seem to get an error.


Any help would be appreciated.

Kind Regards,

Tipler93

 

[Aladin Akyurek]

Hey Everyone,

I have an Excel document with two sheets(tabs).

One is called "AnalyseData" and the other is called "New Table".

I want to get the total of values in column K in "AnalyseData" WHEN:

Column F in AnalyseData = Cell D12 in New Table
Column D in AnalyseData = Cell B2 in New Table
Column D in AnalyseData = Cell B3 in New Table

This is my current formula:

=SUMIFS(AnalyseData!$K:$K,AnalyseData!$F:$F,'New Table'!$D$12,AnalyseData!$D:$D,'New Table'!$B$2,AnalyseData!$D:$D,'New Table'!$B$3)


Now I want to be able to filter the raw data in AnalyseData and make it so the formula only sums up the visible cells in column K. I have tried to use Subtotal and Sumproduct but I always seem to get an error.


Any help would be appreciated.

Kind Regards,

Tipler93

Try...

=SUMPRODUCT(
    AnalyseData!$K$2:$K$200,
    --(AnalyseData!$F$2:$F$200='New  Table'!$D$12),
    --ISNUMBER(MATCH(AnalyseData!$D$2:$D$200,'New Table'!$B$2:$B$3,0)))