Combining SUMPRODUCT and Subtotal Functions with same criteria

[corinaas]

I have an existing table that uses SUMIFS of a range on another sheet and works great, but we need to add a subtotal function so it only totals the filtered data.

This is what I have so far from tutorial boards but it's returning a #NAME? error: =SUMIPRODUCT(SUBTOTAL(109,(OFFSET(DataSheet!$E:$E,DataSheet!$F:$F,TimeSheet!$A3,DataSheet!$G:$G,B$1)))).

Here is the equation that works but does not subtotal: =SUMIFS(DataSheet!$E:$E,DataSheet!$F:$F,TimeSheet!$A3,DataSheet!$G:$G,B$1)

How can I amend to both SUMIF and Subtotal the same data range and critieria?

 

[Fluff]

Hi & welcome to MrExcel.
Rather than using sumproduct/offset, which is volatile & can be slow. I would suggest using a helper column with

=SUBTOTAL(3,A2)

Change the A2 to a column that will always have data & then you can use

=SUMIFS(DataSheet!$E:$E,DataSheet!$F:$F,TimeSheet!$A3,DataSheet!$G:$G,B$1,DataSheet!$X:$X,1)

change column X to the column with the subtotal.

 

[corinaas]

Hi & welcome to MrExcel.
Rather than using sumproduct/offset, which is volatile & can be slow. I would suggest using a helper column with

=SUBTOTAL(3,A2)

Change the A2 to a column that will always have data & then you can use

=SUMIFS(DataSheet!$E:$E,DataSheet!$F:$F,TimeSheet!$A3,DataSheet!$G:$G,B$1,DataSheet!$X:$X,1)

change column X to the column with the subtotal.

This worked swimmingly! Really appreciate your quick response and expertise!

 

[Fluff]

You're welcome & thanks for the feedback.