totals

[Joanna_gr]

Hallo! can u please help me on this? it's a kind of an urgent!

I have 2 columns like following:
A B
opt. 100
pess. 50
opt. 150
pess. 80
Total

I want to create a scenario where the total should be based on column A (for example in C1 i write "opt." Then the total should summarize only the cells in column B where in col. A is "opt.". Can u please help me with this? Thanks u very much

[Audiojoe]

Sure, try this:

=SUMPRODUCT((A1:A65336="OPT")*(B1:D65336))

[Joanna_gr]

Thanks for this, but i'm afraid i didn't make myself quite clear. What i want is to get the sum of "opt." when the cell C1 is "opt.". But if the cell C1 is "pes." then i get the sum of the "pes." cells. Hope that helps more

On 2002-04-11 06:01, Joanna_gr wrote:
Hallo! can u please help me on this? it's a kind of an urgent!

I have 2 columns like following:
A B
opt. 100
pess. 50
opt. 150
pess. 80
Total

I want to create a scenario where the total should be based on column A (for example in C1 i write "opt." Then the total should summarize only the cells in column B where in col. A is "opt.". Can u please help me with this? Thanks u very much

[Steve Hartman]

=SUMIF(A1:A65336,C1,B1:B65336) will work for all of column A and B. Adjust the ranges as needed.



[ This Message was edited by: Steve Hartman on 2002-04-11 06:47 ]

[Aladin Akyurek]

On 2002-04-11 06:03, Audiojoe wrote:
Sure, try this:

=SUMPRODUCT((A1:A65336="OPT")*(B1:D65336))

Audiojoe,

SUMPRODUCT is seldom needed for summing/counting in situations with a single condition/criterion. SUMIF and COUNTIF would be more efficient to use.

Regards,

Aladin

[Derek]

Hi Joanna

You could try this CSE macro, change the range to suit your range and note that there is a dot after opt. and pess. so it must be identical in C1

As this is an array macro you must hold down control+shift+enter to enter it (it will then automatically acquire curly braces around it)

=IF(C1="opt.",SUMIF(A5:B10,"opt",B5:B10),SUMIF(A5:B10,"pess.",B5:B10))

Hope this helps
regards
Derek

[Audiojoe]

Please accept my apologies

[Joanna_gr]

THANK YOU! THANK YOU! THANK YOU! U r really very helpfull as always

On 2002-04-11 06:18, Steve Hartman wrote:
=SUMIF(A1:A65336,C1,B1:B65336) will work for all of column A and B. Adjust the ranges as needed.



[ This Message was edited by: Steve Hartman on 2002-04-11 06:47 ]

[Joanna_gr]

well, another one little thing!! a bit urgent too!

What should i do if C1 = opt. and i want to get the sum of opt. & pess., but if C1 is pess. i need to get the sum of pess. only!!!

(for example :
C1= opt
A / B
opt / 50
opt / 100
pess / 50
total 200
If C1 = pess
then total 50

Can u please help to that also??

[Steve Hartman]

Are opt and pess the only possible entries in column A?