(Sub)totals

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



0
kwakkel (12)
3/17/2005 9:10:41 AM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
1101 Views

Similar Articles

[PageSpeed] 0

Hi

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

Cheers
JulieD


"Kwakkel" <kwakkel@skynet.be> wrote in message 
news:42394995$0$14966$ba620e4c@news.skynet.be...
> 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 ;)
>
>
> 


0
JulieD1 (2295)
3/17/2005 9:34:01 AM
Excellent!
Thanks a lot. It seems this solutions works :)
Regards.


"JulieD" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
news:eCDnlStKFHA.1172@TK2MSFTNGP12.phx.gbl...
> Hi
>
> i would use a SUMIF function
> =SUMIF(F:F,"Sector 1",B:B)
>
> Cheers
> JulieD
>
>
> "Kwakkel" <kwakkel@skynet.be> wrote in message 
> news:42394995$0$14966$ba620e4c@news.skynet.be...
>> 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 ;)
>>
>>
>>
>
> 


0
kwakkel (12)
3/17/2005 9:57:50 AM
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" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
news:eCDnlStKFHA.1172@TK2MSFTNGP12.phx.gbl...
> Hi
>
> i would use a SUMIF function
> =SUMIF(F:F,"Sector 1",B:B)
>
> Cheers
> JulieD
>
>
> "Kwakkel" <kwakkel@skynet.be> wrote in message 
> news:42394995$0$14966$ba620e4c@news.skynet.be...
>> 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 ;)
>>
>>
>>
>
> 


0
kwakkel (12)
3/17/2005 11:55:08 AM
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.

-- 
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets



"Kwakkel" <kwakkel@skynet.be> wrote in message
news:42394995$0$14966$ba620e4c@news.skynet.be...
> 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 ;)
>
>
>


0
garbage (651)
3/17/2005 12:36:44 PM
Hi

is

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

what you're looking for?

Cheers
JulieD

"Kwakkel" <kwakkel@skynet.be> wrote in message 
news:42397022$0$17999$ba620e4c@news.skynet.be...
>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" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
> news:eCDnlStKFHA.1172@TK2MSFTNGP12.phx.gbl...
>> Hi
>>
>> i would use a SUMIF function
>> =SUMIF(F:F,"Sector 1",B:B)
>>
>> Cheers
>> JulieD
>>
>>
>> "Kwakkel" <kwakkel@skynet.be> wrote in message 
>> news:42394995$0$14966$ba620e4c@news.skynet.be...
>>> 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 ;)
>>>
>>>
>>>
>>
>>
>
> 


0
JulieD1 (2295)
3/17/2005 3:52:02 PM
=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" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
news:O%23kQ1lwKFHA.2716@TK2MSFTNGP15.phx.gbl...
> Hi
>
> is
>
> =SUMPRODUCT(--(F1:F100="Sector 1"),(S1:S100*B2:B100))/SUM(B:B)
>
> what you're looking for?
>
> Cheers
> JulieD
>
> "Kwakkel" <kwakkel@skynet.be> wrote in message 
> news:42397022$0$17999$ba620e4c@news.skynet.be...
>>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" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
>> news:eCDnlStKFHA.1172@TK2MSFTNGP12.phx.gbl...
>>> Hi
>>>
>>> i would use a SUMIF function
>>> =SUMIF(F:F,"Sector 1",B:B)
>>>
>>> Cheers
>>> JulieD
>>>
>>>
>>> "Kwakkel" <kwakkel@skynet.be> wrote in message 
>>> news:42394995$0$14966$ba620e4c@news.skynet.be...
>>>> 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 ;)
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
kwakkel (12)
3/17/2005 4:49:17 PM
 =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" <kwakkel@skynet.be> wrote in message 
news:4239b513$0$30175$ba620e4c@news.skynet.be...
> =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" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
> news:O%23kQ1lwKFHA.2716@TK2MSFTNGP15.phx.gbl...
>> Hi
>>
>> is
>>
>> =SUMPRODUCT(--(F1:F100="Sector 1"),(S1:S100*B2:B100))/SUM(B:B)
>>
>> what you're looking for?
>>
>> Cheers
>> JulieD
>>
>> "Kwakkel" <kwakkel@skynet.be> wrote in message 
>> news:42397022$0$17999$ba620e4c@news.skynet.be...
>>>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" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
>>> news:eCDnlStKFHA.1172@TK2MSFTNGP12.phx.gbl...
>>>> Hi
>>>>
>>>> i would use a SUMIF function
>>>> =SUMIF(F:F,"Sector 1",B:B)
>>>>
>>>> Cheers
>>>> JulieD
>>>>
>>>>
>>>> "Kwakkel" <kwakkel@skynet.be> wrote in message 
>>>> news:42394995$0$14966$ba620e4c@news.skynet.be...
>>>>> 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 ;)
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
JulieD1 (2295)
3/17/2005 4:59:33 PM
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" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
news:O7Uc5MxKFHA.3960@TK2MSFTNGP09.phx.gbl...
> =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" <kwakkel@skynet.be> wrote in message 
> news:4239b513$0$30175$ba620e4c@news.skynet.be...
>> =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" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
>> news:O%23kQ1lwKFHA.2716@TK2MSFTNGP15.phx.gbl...
>>> Hi
>>>
>>> is
>>>
>>> =SUMPRODUCT(--(F1:F100="Sector 1"),(S1:S100*B2:B100))/SUM(B:B)
>>>
>>> what you're looking for?
>>>
>>> Cheers
>>> JulieD
>>>
>>> "Kwakkel" <kwakkel@skynet.be> wrote in message 
>>> news:42397022$0$17999$ba620e4c@news.skynet.be...
>>>>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" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
>>>> news:eCDnlStKFHA.1172@TK2MSFTNGP12.phx.gbl...
>>>>> Hi
>>>>>
>>>>> i would use a SUMIF function
>>>>> =SUMIF(F:F,"Sector 1",B:B)
>>>>>
>>>>> Cheers
>>>>> JulieD
>>>>>
>>>>>
>>>>> "Kwakkel" <kwakkel@skynet.be> wrote in message 
>>>>> news:42394995$0$14966$ba620e4c@news.skynet.be...
>>>>>> 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 ;)
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
kwakkel (12)
3/17/2005 5:45:54 PM
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" <kwakkel@skynet.be> wrote in message 
news:4239c258$0$20667$ba620e4c@news.skynet.be...
> 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" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
> news:O7Uc5MxKFHA.3960@TK2MSFTNGP09.phx.gbl...
>> =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" <kwakkel@skynet.be> wrote in message 
>> news:4239b513$0$30175$ba620e4c@news.skynet.be...
>>> =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" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
>>> news:O%23kQ1lwKFHA.2716@TK2MSFTNGP15.phx.gbl...
>>>> Hi
>>>>
>>>> is
>>>>
>>>> =SUMPRODUCT(--(F1:F100="Sector 1"),(S1:S100*B2:B100))/SUM(B:B)
>>>>
>>>> what you're looking for?
>>>>
>>>> Cheers
>>>> JulieD
>>>>
>>>> "Kwakkel" <kwakkel@skynet.be> wrote in message 
>>>> news:42397022$0$17999$ba620e4c@news.skynet.be...
>>>>>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" <JulieD@hctsReMoVeThIs.net.au> schreef in bericht 
>>>>> news:eCDnlStKFHA.1172@TK2MSFTNGP12.phx.gbl...
>>>>>> Hi
>>>>>>
>>>>>> i would use a SUMIF function
>>>>>> =SUMIF(F:F,"Sector 1",B:B)
>>>>>>
>>>>>> Cheers
>>>>>> JulieD
>>>>>>
>>>>>>
>>>>>> "Kwakkel" <kwakkel@skynet.be> wrote in message 
>>>>>> news:42394995$0$14966$ba620e4c@news.skynet.be...
>>>>>>> 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 ;)
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
JulieD1 (2295)
3/19/2005 1:36:24 PM
Reply:

Similar Artilces:

Duplicating MainForm, SubForm and Sub-SubForm
Hello Everyone, I have an Order Entry Form composed of a MainForm, a SubForm and 2 Sub- Subforms. The SubForm is a continuous form. The two Sub-SubForms are children of a SubForm via a foreign key (which is the Primary Key of a SubForm). There are some instances when customers revised their order by changing just the amount of orders or by just removing one item and the rest are the same. To do this, I would like to keep the initial order (record) and create another record by duplicating the first order then change the amount (or remove an item whatever the case maybe) on the new record. The...

sum total hours and minutes
ok I have read all the questions and answers and I still can't get my program to work: here is what I do: July Event StartTime End Time 8 Purchase cat food / diet pepsi / rug cleaner 1:30 PM 2:15 PM 00:45 15 Pay house gas and phone bills 10:00 AM 10:30 AM 00:30 the format I use for start time is: TIME 1:30PM the format I use for end time: TIME 2:15PM the last column formula is: =TEXT(D4-C4,"hh:mm"...

Running Total 11-23-09
Hi, I would like to be able to have a running total for the month. date sales 10/11/2009 100 11/11/2009 50 12/11/2009 10 therefor the total as of 12/12009 will be 160 and run until the end of the month and then to start from zero and begin running the total again. thanks for your help M >>therefor the total as of 12/12009 will be 160 - I believe you mean 12/1/2009. Your words do not match the math -- Your words say to start over at the begining of each month so on 12/1/2009 ...

Outlook 2000 Sub folder
Hi I am using outlook 2000 and I have a created a subfolder in my inbox however I am unable to view the folder My Shortcuts on the left hand side of the screen where all my other sub folders are located. In the navigation bar at the top of my pane there is an icon called "move to Folder" and the folder is located here. I am unable to find the location of the folder therefore unable to open the folder or move it to "My shortcuts" I am in need of your expertise. I am ok with computers and can follow instructions so please please please help. Thanking you in ...

Money 2000 and Euro wrong total
Hi evereybody I use Money 2000 Pro (vers. 8.0) and my primary currency since 2002 is Euro (I live in Italia, Eu) I have a problem since I changed for the former currency (italian lira) to actual currency (Euro)... (My older bills were in Lira so I've changed them manually to converse in Euros.) I made changes on my multiple bills to join new currency and it works fine (Now I've 2 bills [1999-2006] in Euro) but... When I see to the main page with both bills summary the total of money is wrong. For example: Bill 1 : 1000,00 Euro Bill 2 : 2000,00 Euro SubTotal (Sum of Bill 1 + Bill 2...

What are the formula for calculating ROI all dates (Total return all dates)
From http://money.mvps.org/articles/portfolio_columns.aspx, it says that dividends should be included in calculation of ROI all dates(Total return all dates) So the formula for ROI should be ((market price - purchase price)+ dividend) / purchase price. Is this correct? However, what I am seeing is that ROI all dates is calculated as (market price - purchase price)/purchase price. Is there any other parameter that includes dividends also In microsoft.public.money, rvsw@hotmail.com wrote: > From http://money.mvps.org/articles/portfolio_columns.aspx, it says >that dividends should be i...

sub-process
What is the sub-process function in the workflow? How do you set one up? Why would you use it? A sub-process is a way to call a manual rule. If you create a manual rule in workflow called Rule A, in another rule you can insert a sub-process and select the manual rule to run. This is a helpful way to create loops and if/then rules that trigger other events. -- Matt Wittemann http://icu-mscrm.blogspot.com "David M" wrote: > What is the sub-process function in the workflow? > How do you set one up? > Why would you use it? > ...

Growing Total using Autofilter in Excel?
Hello, I have a spreadsheet i am working on in Excel 2002. I have a ton of data which i am sorting to date using AutoFilter, which is working fine. when the data is not filtered i have a rolling total which adds up the whole thing like this. a b 1 100 100 2 100 200 3 150 350 but when i use the filter i see the larger totals in the list (which i understand) What i want is the same thing in a new column, but for only the current data from the view. example a b ~ 7 150 150 8 100 250 9 200 450 I have tried using sum and subtotal but it doesnt allow + example: =sum(b7+a8) and =subtotal(9,b7+a8...

Sub, Private Sub, Public Sub
I got a different lap top and when I moved my Personal.xls from one to the other some of my macros were not on the list of macros when you go to tools>macros. Although when I look in the code they are there. I figured out that the ones that were "Private Sub" macros were no working. I also figured that when I changed them to just "Sub" they worked. I do know that if "It aint broke don't fix it, cus to try is usually to fix it till it is broke." But what is the difference between "Sub", "Private Sub", & "Public ...

Cascade current view to sub-folders
Hi, Is there any way I can set a custom View setting to a set of folders (e.g. set a view setting for a high-level folder and then have it cascade to all sub-folders)? By a custom View I mean the way that items in a folder shoud look - i.e. font settings and the like. Cheers, Stu ...

(Sub)totals
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 (n...

Great Plains payroll transaction entry total dollars per employee
When I enter transactions in payroll batches, I would like to see the entry window show me a dollar total (rate x hours) as I'm entering the transactions (per line) and then a dollar total per employee. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.mi...

Shift wise Totals
I have a shift 6 running after midnight, hence the productions shows on the next day, can I have the production for the same day. The shifts timings are shift 1 - 6am - 9am, shift 2 - 9:30am _12:30pm, shift3 - 1pm - 4pm, shift 4 - 5pm-8pm, shift 5 - 8:30pm:1130pm, & shift6 - 00am - 2:30am Thats the production for the day, but since shift 6 falls after midnight, all that is process falls on the next day, while actually the production is for the previous day. Subtract, logically, 2h30 ? SELECT ..., SUM( something) ,... FROM ... GROUP BY DateValue( dateTimeStamp - #02:30:00# ) Vande...

A way to create sub-categories?
I want to group contacts by category and then a sub-category for easy viewing. Apart from creating a new field for sub-category is there a way of doing this? Outlook doesn't seem to support sub-categories. Thanks Nope. -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Programmer's Reference Author - Absolute Beginner's Guide to Microsoft OneNote 2003 http://blogs.officezealot.com/cardoza http://www.cardozasolutions.com "Casebase" <junkmail@casebase.net> wrote in message news:%23rx5a...

Microsoft Office Doc Image Writer is still printing ghosts and it's totally freaking me out!!!!!!!
Every time i use 'Microsoft Office (2003) Doc Image Writer' (no printer at home) no printed document appears - ever!!! But when I try to reprint the document it's 'dis-embodied' ghost appears only in the save as dialogue box (even after a reboot) I think this is yet another Vista bug (Vista can't see/find existing documents) I've reported this before without cure Anyone know the name of it's predecessor (we use at work) which works perfectly - creates it's own document type albeit can't remember the extension I cannot repeat the problem. But ...

Are sub-totals able to be set to be bold automatically?
I have a large amount of sub-totals, which at the moment are not bold unless I do a manual job of making them so. Is there any way of automating this process in the sub-total procedure? You can use conditional formatting to bold the rows. There are instructions in Excel's Help files, and on the following page: http://www.contextures.com/xlCondFormat01.html Select the columns that contain the table Choose Format>Conditional Formatting From the first dropdown, choose Formula Is In the textbox, enter a formula that refers to a column in which the "Total" text appears...

Sub or Function not defined?!
In a further attempt to debug the disabled=false problem, I wrote out some enabling code in the subforms and call it from the parent. Here is the code in the subform... Public Sub UpdateFillsFormState(canEdit) QuantityField.Enabled = canEdit PriceField.Enabled = canEdit TaxField.Enabled = canEdit NetField.Enabled = canEdit If Me.RecordsetClone.recordCount > 0 And QuantityField.Enabled = True Then QuantityField.SetFocus End Sub Over in the main form I wait until I know the form is loaded and set up properly, then I do this... Public Sub SetupFillState() canEdit = isEditable() Call Update...

Sub error
I have created this macro but everytime I run it the Columns it hides is incorrect. It hides FGHIJK it should only hide GHI. Can anyone tell me why this is happening? Thank you for your help. Sub Done_Changes() ' ' Done_Changes Macro ' ' Range("G11:H50").Select Selection.Locked = True Selection.FormulaHidden = False ActiveWindow.SmallScroll Down:=42 Range("G54:H58").Select Selection.Locked = True Selection.FormulaHidden = False ActiveWindow.SmallScroll Down:=9 Range("G62:H74").Select Selection.Locked = T...

Filter by Form with sub form crash
Hi I'm having problems with filter by form causing access 2003 to shut down. Looking at the Northwind database the same thing happens. If a main form has "new additions" setting to no and has sub forms filter by form is used and the data serched on the main fom returns null ( eg some one filters Michal insted of Michael, access crashes. My own forms are linked to queries which results in not allowing "new additions" so this has the same effect in access shutting down. any adivce would be great -- with thanks Mike Mike, I just tried this in Access 2003 ...

disabling all menu and its sub menus
hi all, how to disable entire menu and its sub menus. i tried the following code it gets the Menu ID but is not disabling the menu items. HMENU hMenu; HWND hwnd = GetSafeHwnd(); hMenu = ::GetMenu(hwnd); int nCount=GetMenuItemCount(hMenu); for (int i=0;i<nCount;i++) { //get the sub menu HMENU hSubMenu=GetSubMenu(hMenu,i); //get the number of items in the submenu and disable them individually. int nSubItems=GetMenuItemCount(hSubMenu); for (int j=0;j<nSubItems;j++) { HMENU hSubMenu=GetSubMenu(hMenu,i);. int nSubItems=GetMenuItemCount(hSubMenu); EnableMenuItem(hSubMenu,G...

running a Sub in a class module from a functionin a general module
If I have a sub in the module for a form named frmActivity which contains a procedure named Sub cmd_close_form_Click() is there a way to call that Sub and run the procedure from a function in a general module? Thanks in advance, Paul "Paul" <begone@spam.com> wrote in message news:%23E1556FqKHA.5840@TK2MSFTNGP04.phx.gbl... > If I have a sub in the module for a form named frmActivity which contains > a procedure named > > Sub cmd_close_form_Click() > > is there a way to call that Sub and run the procedure from a function in...

How do I import sub.....
Ok, I have my personal contacts folder. It is called by default "contacts" I have out public contacts folder. It is called "IT contacts" How do I get the info from IT contacts into my personal contacts. I tried exporting a personal file, then importing it into my personal contacts, and it did, in a subfolder. I don't want it in a subfolder (my pda won't read it) I want it added to my personal contacts folder... make sense? Please help... jojo (Outlook 2000) Ctrl+drag and drop the individual items. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook ...

sorting cells in a sub routine
I'm trying to Sort a block of cells as a step in a routine. The size of the block varies, but it will always be Sorted in Ascending order by the last column. The routine works by having Excel evaluate the CurrentRegion of the ActiveCell with the CurrentRegion cells surrounding it. If they match, the sort fires. All of this works fine except when the sheet is protected. As far as I can tell, CurrentRegion causes an error message when the sheet is protected. How else can I establish the area to be sorted? I'm currently doing it by finding the first and last Columns in the Region an...

Excluding multiple checking accounts from budget totals?
Howdy! Running Money06, and I have two checking accounts synching through Bank of America. Everything there is working well, but one thing that I dont like is that the totals for BOTH accounts are added together. I have two accounts, 'personal' and 'class', and both accounts are shown in the net balance statements, the 'spending by catagory' chart on the home page etc. I would like to keep synched with my class account, but want it excluded from all of the balances.. any suggestions? "Raichean" <Raichean@discussions.microsoft.com> wrote in mes...

Sync all sub folders from a specific public folder
Hi, I know how to sync a single public folder, but is there an easier way to sync a public folder and include all levels of sub directiores related? Thanks in advance Katrin Katrin wrote: > Hi, > > I know how to sync a single public folder, but is there an easier way > to sync a public folder and include all levels of sub directiores > related? > > Thanks in advance > > Katrin You have to drag all folders to favorites in order to sync them to offline files. Subfolders too. Depending on your v. of Outlook you may be able to easily select all PF favorites for s...