(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

 

[JulieD]

Hi

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

Cheers
JulieD

 

[Kwakkel]

Excellent!
Thanks a lot. It seems this solutions works
Regards.

 

[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 )

 

[Arvi Laanemets]

Hi

When you need to view totals for various groups, then for such cases I
sometimes use SUBTOTAL function and autofilter.
P.e. header row is row 3, and you want to display the sum of filtered data
for columns E and F and to count filtered rows in column C (in column C are
text values), at first row.

C1=SUBTOTAL(3,RangeC)
E1=SUBTOTAL(9,RangeE)
F1=SUBTOTAL(9,RangeF)
(replace RangeC, RangeE and RangeF in example formulas with real range
references, or with dynamic named ranges)

Now, when you use autofilter on table, at top of sheet according sums/counts
are displayed.

 

[JulieD]

Hi

is

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

what you're looking for?

Cheers
JulieD

 

[Kwakkel]

=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]

=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]

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]

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