totals

Joanna_gr

Board Regular
Joined
Feb 16, 2002
Messages
149
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Upvote 0
=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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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??
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top