Thanks:  0
Likes:  0

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

2. Sure, try this:

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

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

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

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

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

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

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

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

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

## 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
•