Subtotal and sumif help

[Guest]

I need to be able to filter my data by serial number and return the total
quantity, then multiply by a constant number.

This is the formula I have thus far, but I don't know how to subtotal it...

=SUMIF(C1:C53,E55,F1:F53)*(0.085)

METER PRIOR CURRENT QTY SERIAL NO.
BLACK 95 2125 2030 KNF11183 IRC 3170
COLOR 144 2072 1928 KNF11183 IRC 3170
BLACK 227 382 155 KNF11467 IRC 3170
COLOR 69 423 354 KNF11467 IRC 3170
BLACK 63 1350 1287 KNF11474 IRC 3170
COLOR 34 942 908 KNF11474 IRC 3170

Any assistance would be appreciated!

 

[Guest]

here are a couple of options for you. It will be different depending on how
you really want it to look.

1. Subtotals
a) add a new column in your data where you multiply the QTY for every row by
your constant
b) sort by serial number
c) use data > subtotals. At each change in serial number, use function SUM
on your new column

2. Sumif
cell A1 has a serial number for which you want the subtotal
in cell B1 you can use
=SUMIF(column with serial numbers,A1,column with qty) * 0.085
but this is not really a subtotal.