Thanks:  0
Likes:  0

1. 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

2. 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

3. 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

4. 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?

5. 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

6. 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

7. 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

8. 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.

[ This Message was edited by: Aladin Akyurek on 2002-04-11 08:42 ]

9. 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!

10. 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,

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•