Counting (sub)totals

[Kwakkel]

Hello everybody.
I have the following problem:
I have an Excel file with Column B 'Quantity' and column F 'Sector'. Now i
need to count all the quantities within the same sector
Sorting + AutoSum isn't an option, since the file has other calculations in
it as well, that also depend on a sort.
There's in my Dutch version a function 'DBSUM' and a function
'TURNTABEL.FETCH' ... these are translated terms, so I don't know if they
match with English versions ... but could someone please tell me how these
functions work, since i can't get them right (not by typing everyting
manually, nor by using Excels 'function input window'.
So, if you could help me out on one of these 2 functions, OR offer me an
alternative, i'll be close to eternally gratefull

 

[Guest]

Hello there,

Use Subtotal formula.

Place this in Cell F15 for instance, +SUBTOTAL(9;F17:F40), then sort the
data by an autofilter.

Cheers.

 

[Kwakkel]

+SUBTOTAAL(10002;F2:F126)

When i enter the previous line (SUBTOTAAL = SUBTOTAL in Dutch ), i get
the error "#VALUE".
I also don't really understand what this function does. The idea is i count
all the numbers in column B together, if column F has a specific value
(10002 to 10014).
So if my sheet looks like this:
B F
500 10003
500 10002
10000 10003
200 10002

i would want
<RANDOM CELL 1> <RANDOM CELL 2>
10002 700
10003 1500

Also, the length of the columns (currently 2 to 126, since 1 is a header)
can change(next week i could have to make those subtotals from 2 to 146 for
example).

I hope i made myself clear, and I hope you can help me further with this
problem.
The for the previous reply in any case

 

[Kwakkel]

It would seem the SUMIFfunction works here.
Thanks for the reply anyway
I hope some other people can also use this information

 

[Kwakkel]

I now have the following (similar) problem:

I still have columns B and F, but also a column S 'Yas Asw Spread'.

Now i have to make a weighted average from S per sector F.

So:

If F2 = 10002, I'd multiply S2 with B2 and divide by the total of column B.

I thought i'd add the SUMPRODUCT around your SUMIF, but unfortunatly that
doesn't work.

Is there any other way i can solve this, preferably without pivot tables,
since i have to admit, those don't make much sense to me

Thanks in advance (and keeping my fingers crossed )