Combining SUMIF and SUBTOTAL formula

[MrKaliszan]

Hi,

I have a problem combining a SUMIF and SUBTOTAL formula into one formula, so i only get the "shown" filtered rows summed when criteria is met. I hope you can help?


=SUMIF($D$5:D25;”Small";E5:E25)

=SUBTOTAL(9;E5:E25)



$D$5:D25 - is the range to look for the word “Small”

“Small” - is the criteria for which to SUM

E5:E25 - Is the range to sum If criteria is met



However I also have a filter in column B to filter the shown rows in E5:E25 and only want to sum the shown rows meeting the criteria “small” in Column D.


The formula is typed into cell E28
In E29 I have a similar formula looking for “Medium”
And in E30 I have one looking for “Large”

 

[Fluff]

How about using a helper column with

=SUBTOTAL(3,D5)

then you can use

=SUMIFS(E5:E25;$D$5:D25;”Small";F5:F25;1)

change the F5:F25 to the column with the subtotal formula

 

[MrKaliszan]

How about using a helper column with

=SUBTOTAL(3,D5)

then you can use

=SUMIFS(E5:E25;$D$5:D25;”Small";F5:F25;1)

change the F5:F25 to the column with the subtotal formula

As i can tell, this has too many arguments for a SUMIF... And in your answer you changed the sum range to the "look-up range" which i don't understand why.

Is it possible to use a SUMPRODUCT for this?

EDIT: the "range" to look up "small" is static so =SUMIF($D$5:$D$25;”Small";E5:E25)

 

[Fluff]

As i can tell, this has too many arguments for a SUMIF

I did not use sumif, I used sumifs plural

the "range" to look up "small" is static so =SUMIF($D$5:$D$25;”Small";E5:E25)

I just used the range in the formula you posted.

Is it possible to use a SUMPRODUCT for this?

Not without making it slow, volatile & complex.
The solution I suggested is far more efficient & also simpler. Why not try it, rather than just dismissing out of hand?

 

[MrKaliszan]

I did not use sumif, I used sumifs plural

I just used the range in the formula you posted.


Not without making it slow, volatile & complex.
The solution I suggested is far more efficient & also simpler. Why not try it, rather than just dismissing out of hand?

Ah i'm sorry. My mistake. Tried it with with SUMIF... and failed obviously. Thanks for your help. Was not trying to sound unthankful, my apologies if i came across like that. My bad.

 

[Fluff]

Is it working?

 

[MrKaliszan]

Is it working?

It works! Thanks a lot.

You made my day

 

[Fluff]

You're welcome & thanks for the feedback.