SUMPRODUCT with SUBTOTAL

[IanMackayinAmsterdam]

Hi there,

I have a small question about a spreadsheet I'm trying to create.

I have a sheet with a country in column A and a material number in column B and the number of contracts in column D.

Above that I have totals per country, at the moment I'm using a SUMPRODUCT formula to get the totals per country in column D.

=SUMPRODUCT((A17:A2461 = C2)*(D17:D2461))

But what I would really like is to be able to do is also filter on the material number in column B and also get the totals per material number in the above totals (so basically if I filter on 1 material I would get the totals per country on that specifically).

I have googled this and I assume I need to add the subtotal formula somewhere into the above formula, I have tried but I can't seem to get anything to work.

Any help would be greatly appreciated.

Thanks in advance,

Ian

 

[pgc01]

Hi Ian

If you use a Pivot Table you'll get a list with quantities / country / material.

As for the formula, try:

=SUMPRODUCT((A17:A2461 = C2)*SUBTOTAL(3,OFFSET(B17:B2461,ROW(B17:B2461)-ROW(INDEX(B17:B2461,1)),0,1))*(D17:D2461))

 

[IanMackayinAmsterdam]

Thanks pgc01, works perfectly.

Regards,

Ian.