Nest SumIFS inside subtotal

I currently use SUMIFS formula: =SUM(SUMIFS(KZ6:KZ598,$M$6:$M$598,"a",$F$6:$F$598,"car")). Is there a way that if I use a drop down to sort that I can incorporate a Subtotal formula so that the value changes with the sort?

I recommend a helper column.

Say O6 for example
O6 filled down
=SUBTOTAL(103,M6)

Then use
=SUMIFS(KZ6:KZ598,$M$6:$M$598,"a",$F$6:$F$598,"car",$O$6:$O$598,1)

Also not sure why you had SUM(SUMIFS(...))
That sum seemed redundant, so I removed it.

Is there any way to not use a helper column? The spread sheet already has many, many columns. I'm trying to get this to formula to work: =SUBTOTAL(9,SUMIFS(Q6:Q598,$M$6:$M$598,"a",$F$6:$F$598,"car")). So if I sort a column to see only Ford cars, the total sum will change as I sort for car types. Using the SUMIFs works fine for seeing all cars within the range but I can't figure out how to make it a "subtotal function".

Thanks for the redundant "sum" comment. I removed it.

Try this:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(KZ6,ROW(KZ6:KZ598)-ROW(KZ6),0)),(M6:M598="a")+0,(F6:F598="car")+0)

AWESOME! Thank you.

You're welcome. Thanks for the rep!

I've spent the last hour trying to understand what the formula is doing but am not quite sure. Could you clarify? I think I understand SUMPRODUCT is multiplying arrays (Rows KZ, M & F), Subtotal with 9 is summing "visible data in row KZ if it meets conditions in rows M and F. I understand Offset function returns data from a specified cell/range and the data can be summed by specifying the width/height.

Is the formula saying "start at cell KZ6, sum visible data within the range of KZ6-KZ598 (that meets criteria), but really don't offset? What does the zero do at the end of this array and the +zero's in the other arrays?