(Sub)totals
 

Hi

i would use a SUMIF function
=SUMIF(F:F,"Sector 1",B:B)

Cheers
JulieD


"Kwakkel" wrote in message
...
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 ;)

Hi

is

=SUMPRODUCT(--(F1:F100="Sector 1"),(S1:S100*B2:B100))/SUM(B:B)

what you're looking for?

Cheers
JulieD

"Kwakkel" wrote in message
...
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 ;) )

"JulieD" schreef in bericht
...
Hi

i would use a SUMIF function
=SUMIF(F:F,"Sector 1",B:B)

Cheers
JulieD


"Kwakkel" wrote in message
...
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 ;)

=SUMPRODUCT(--(F2:F100="Sector 1"),(S2:S100*B2:B100))/SUM(B2:B100)

should work .... where the data in column S & B is numeric
the "" are needed around the 10002 if it is entered as text - if it is
entered as a number omit them

.... my actual test formula is
=SUMPRODUCT(--(F2:F114="New South Wales"),(I2:I114*J2:J114))/SUM(J2:J114)

have you use the tools / formula auditing / evaluate formula to step through
=SUMPRODUCT(--(F2:F126="10002"),(S2:S126*B2:B126)) / SUM(B2:B126)
to see where it's failing?

Cheers
JulieD


"Kwakkel" wrote in message
...
=SUMPRODUCT(--(F2:F126="10002"),(S2:S126*B2:B126)) / SUM(B2:B126)
gives an error. According to excel (2003), the comma is the guilty one.

=SUMPRODUCT(--(F2:F126="10002"):(S2:S126*B2:B126)) / SUM(B2:B126)
gives an error. This time, excel marks the entire expression.

=SUMPRODUCT(--(F2:F126="10002");(S2:S126*B2:B126)) / SUM(B2:B126)
gives an error. No pop-up this time, just a "#NAME" instead of a real
value.

The difference is I changed the comma to a ';' and a ':' (not really
noticable if you don't pay attention ;) ).
Adding " around 10002, or not, doesn't make a difference either.
On itself, as far as I understand Excel, what you said is what i need.
Unfortunatly, i can't get it to work (yet). I hope you can help me a bit
further :)
Thx again anyway :)

P.S.: I assume, once it works, i can use F:F, S:S and B:B instead of
F2:F126, ....? Won't be a disaster if I can't, but it'd look better ;)

"JulieD" schreef in bericht
...
Hi

is

=SUMPRODUCT(--(F1:F100="Sector 1"),(S1:S100*B2:B100))/SUM(B:B)

what you're looking for?

Cheers
JulieD

"Kwakkel" wrote in message
...
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 ;) )

"JulieD" schreef in bericht
...
Hi

i would use a SUMIF function
=SUMIF(F:F,"Sector 1",B:B)

Cheers
JulieD


"Kwakkel" wrote in message
...
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 ;)

Hi

if you usually use ; in your formulas then yes, you are correct in
replacing , with them

if you'ld like to email me your file direct (or this part of it anyway),
i'll have a look - my email is julied_ng at hcts dot net dot au

Cheers
JulieD

"Kwakkel" wrote in message
...
That is kind of a problem: if I enter it exactly like you did, i can't use
the 'Evaluate' options, they're all greyed out.
If I change ',' to ':', the evaluate options are greyed out as well.
If i change ',' to ';', I get results that, as I see it, can't be correct.
I calculated first with a little detour: B * F for each pair, and then
used the SUMIF(F:F;10002;V:V), where V is the column with the B * F
result. I think those results should be correct, and they differ a lot
from the result I get with your function (with ';' instead of ','!!)
If you want, i can attach the file, a portion of it or a screenshot, but
I'll need to alter the terms to your English version then :)


"JulieD" schreef in bericht
...
=SUMPRODUCT(--(F2:F100="Sector 1"),(S2:S100*B2:B100))/SUM(B2:B100)

should work .... where the data in column S & B is numeric
the "" are needed around the 10002 if it is entered as text - if it is
entered as a number omit them

... my actual test formula is
=SUMPRODUCT(--(F2:F114="New South Wales"),(I2:I114*J2:J114))/SUM(J2:J114)

have you use the tools / formula auditing / evaluate formula to step
through
=SUMPRODUCT(--(F2:F126="10002"),(S2:S126*B2:B126)) / SUM(B2:B126)
to see where it's failing?

Cheers
JulieD


"Kwakkel" wrote in message
...
=SUMPRODUCT(--(F2:F126="10002"),(S2:S126*B2:B126)) / SUM(B2:B126)
gives an error. According to excel (2003), the comma is the guilty one.

=SUMPRODUCT(--(F2:F126="10002"):(S2:S126*B2:B126)) / SUM(B2:B126)
gives an error. This time, excel marks the entire expression.

=SUMPRODUCT(--(F2:F126="10002");(S2:S126*B2:B126)) / SUM(B2:B126)
gives an error. No pop-up this time, just a "#NAME" instead of a real
value.

The difference is I changed the comma to a ';' and a ':' (not really
noticable if you don't pay attention ;) ).
Adding " around 10002, or not, doesn't make a difference either.
On itself, as far as I understand Excel, what you said is what i need.
Unfortunatly, i can't get it to work (yet). I hope you can help me a bit
further :)
Thx again anyway :)

P.S.: I assume, once it works, i can use F:F, S:S and B:B instead of
F2:F126, ....? Won't be a disaster if I can't, but it'd look better ;)

"JulieD" schreef in bericht
...
Hi

is

=SUMPRODUCT(--(F1:F100="Sector 1"),(S1:S100*B2:B100))/SUM(B:B)

what you're looking for?

Cheers
JulieD

"Kwakkel" wrote in message
...
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 ;) )

"JulieD" schreef in bericht
...
Hi

i would use a SUMIF function
=SUMIF(F:F,"Sector 1",B:B)

Cheers
JulieD


"Kwakkel" wrote in message
...
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 ;)