Using SUMIFS with SUBTOTAL and filters

[fozzie_1]

Hi, I've tried to find a solution for this and found a great thread about how to use SUMIF and SUBTOTAL using the SUMPRODUCT function, but it didn't help with using filtered lists.

Can someone help me with this problem.

I've created a simple table to demonstrate the requirement but essentially, I'm trying to sum the values of one column '£Value' when the value in another column 'Period' equals Q1, but also taking into account the filter choices I make - in this case where Product = Blue, Product Cat = Peelers and ID does not include Blanks.

Then I wish to repeat the same formula where the value in 'Period' equals Q2 etc etc.

I've tried using this formula, which works great with the filters, but its missing the element which deals with 'only return results where Period = Q1 etc.

=SUMPRODUCT(($C$2:$C$27)*(SUBTOTAL(3,OFFSET($C$2,ROW($B$2:$C$27)-MIN(ROW($C$2:$C$27)),,))))

Can someone help?. I'm sure its easy, but I'm a bit kak at excel. :-)

Thanks

Fozzie_1

[Fotis1991]

Perhaps.

=SUMPRODUCT((Table1[Period]=A31)*$C$2:$C$27)*(SUBTOTAL(3,OFFSET($C$2,ROW($B$2:$C$27)-MIN(ROW($C$2:$C$27)),,)))

[fozzie_1]

thanks Fotis.

I did have the formula wrong, (B instead of C in one of the ranges) - so I've amended that first.
But, after adding in the suggestion you made, its still not returning the results as per the forumla check in C31 to C34.

Do you have any more suggestions on how to make this work? Re-attached with your formula added in.

Thanks

Fozzie_1

[Fotis1991]

Formula, should be this.

Please Login or Register  to view this content.

Also we can use this shorter formula.

Please Login or Register  to view this content.

I think..it's OK, now.

[JosephP]

perhaps
=SUMPRODUCT((Table1[Period]=A31)*(SUBTOTAL(9,OFFSET($C$2,ROW($C$2:$C$27)-MIN(ROW($C$2:$C$27)),,1))))

[fozzie_1]

Fotis, that is brilliant, thanks very much :-) And also thanks for responding so quickly.

Fozzie_1