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

 

[Joanna_gr]

opt - pess - real
are all the options

On 2002-04-11 07:50, Joanna_gr wrote:
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??

 

[Joanna_gr]

can u help me with that too pleeeeease?

On 2002-04-11 07:54, Joanna_gr wrote:
opt - pess - real
are all the options

On 2002-04-11 07:50, Joanna_gr wrote:
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??

 

[Derek]

Hi

Not very elegant, but this seems to work. If opt. and pess. are the only entries in column A the formula could be shortened

=IF(C1="pess.",SUMIF(A1:A65336,"pess.",B1:B65336),SUMIF(A1:A65336,"pess.",B1:B65336)+SUMIF(A1:A65336,"opt.",B1:B65336))

regards
Derek

 

[Yogi Anand]

Well Joanna:
If your data is limited to what you described above,
1) your total for both opt and pess will be
=sum(A1:A3)
2) =sumif(A1:A3,C1,B1:B3)

It looks too simple -- are you sure that's all you want?

 

[Joanna_gr]

Derek thanks, but i have 3 options:

opt. / pes. / real how about in such case?

On 2002-04-11 08:09, Derek wrote:
Hi

Not very elegant, but this seems to work. If opt. and pess. are the only entries in column A the formula could be shortened

=IF(C1="pess.",SUMIF(A1:A65336,"pess.",B1:B65336),SUMIF(A1:A65336,"pess.",B1:B65336)+SUMIF(A1:A65336,"opt.",B1:B65336))

regards
Derek

 

[Derek]

Hi Joanna

This will ignore everything in A except opt. and pess. Do you want it to sum real. if "real" is typed in C1 as well?

regards
Derek

 

[Joanna_gr]

Let me make it more clear!

If the cell A1 says "Opt" I sum "Opt" & "Pess" & "real", if A1 says "Real" i sum "pess" & "real" and if A1 says "P" i sum only the "p" cells

 

[Aladin Akyurek]

On 2002-04-11 08:25, Joanna_gr wrote:
Let me make it more clear!

If the cell A1 says "Opt" I sum "Opt" & "Pess" & "real", if A1 says "Real" i sum "pess" & "real" and if A1 says "P" i sum only the "p" cells

Joanna,

The way you specify the conditions is a bit unusual. So be it.

=(C1="Opt")*(SUM(B1:B100))+(C1="Real")*(SUMIF(A1:A100,"Real",B1:B100)+SUMIF(A1:A100,"Press",B1:B100))+(C1="Press")*(SUMIF(A1:A100,"Press",B1:B100))

where C1 houses the desired condition, will do what you want, given the set of possible conditions.

Aladin
This message was edited by Aladin Akyurek on 2002-04-11 08:42

 

[Yogi Anand]

Hi Joanna:
Following up on where Derek took us, if your item and qty information is in columns A and B for "opt", "pess", and "real", and your lookup value (criterion is in cell C1, then use the formula:

=IF(C1="opt",SUMIF(A:A,"opt",B:B)+SUMIF(A:A,"pess",B:B)+SUMIF(A:A,"real",B:B),IF(C1="real",SUMIF(A:A,"pess",B:B)+SUMIF(A:A,"real",B:B),IF(C1="pess",SUMIF(A:A,"pess",B:B))))

This is not elegant -- but it works. You may also want to explore using D-functions!

 

[Aladin Akyurek]

On 2002-04-11 06:29, Derek wrote:
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

Derek,

I'm a bit surprised by your new terminolgy CSE macro and array macro. Why do you think an array-formula is a macro?

Regards,

Aladin