Value for A & B

maxcorex

New Member
Joined
Jul 29, 2007
Messages
20
Dear all

I have tried defining A=10 and B=20 C=30 D=40
When i put SUM(A+B) the figure is correct which is 30

But how do i just click SUM and use my mouse to drag the cells with A B C D and the sum is shown 100.

I trid and it shows 0. I knw i have to name or label it for even if A is shown it is 10 value. But if we just enter A and B and when you sum it, you cant unless i key into the formula area SUM(A+B)

HELP!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, welcome to the board!

I'm not sure why you need to SUM and drag the mouse. (BTW, Excel wouldn't let me define a name with just C)

Say your letters are in A1:A100. Assuming only A through D or blank are possibilities, how about something like this:

Code:
=SUM(IF(A1:A100<>"",(CODE(UPPER(A1:A100))-64)*10))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.
 

maxcorex

New Member
Joined
Jul 29, 2007
Messages
20
i still dont get it

im confused on what u posted..lets see if u can understand this..
Lets say ive define that A = 10 and B =20 ( how do i define this i tried insert and name then define)

In the columns i just key in A then another colum B..

I also created another column which reads the sum of all the A and B i have entered. How do i do that?...So lets says whenever i key in A A B B A A ..the Sum column actually calculates it as 10+10+20+20+10+10 qhich equals to 80

How do i do this?

i can only get the figure if i type manually in the total column as Sum(A+A+B+B+A+A) then only i can get it..any idea?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You don't need to define it, just let the formula figure it out, or if you have a lot more values it can be done a different way.

The formula I gave you before is in C1. Is this not what you want?
Excel Workbook
ABC
1A80
2A
3B
4B
5A
6A
Sheet
 

maxcorex

New Member
Joined
Jul 29, 2007
Messages
20
Hmm

So far the formula calculate A and B as 10...how do i make it calculate that whenever i key in A its 10 and B its 20 and C 30 for eg..
 

maxcorex

New Member
Joined
Jul 29, 2007
Messages
20
schedule

I get ur formula but i dont get the 64 thingy

what im doin is calculating staff hours..schedule

AA = 9 hrs
AM = 7.7 hrs
DA = 7.5 hrs
EV = 10.5 hrs


I need a formula or how to calculate all this when i key it in into excel to calculate how many hours worked..Help me thaks
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
OK, the formula I gave you before won't work because you don't actually have the values A, B, C, D and they are not multiples of 10 as you indicated.

Perhaps:
=SUM(IF(ISNUMBER(MATCH(A1:A100,{"AA","AM","DA","EV"},0)),LOOKUP(A1:A100,{"AA",9;"AM",7.7;"DA",7.5;"EV",10.5})))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.
 

Forum statistics

Threads
1,181,649
Messages
5,931,217
Members
436,784
Latest member
amuljono

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
Top