Value for A & B

[maxcorex]

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!

 

[Scott Huish]

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:

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

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

 

[maxcorex]

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]

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
	A	B	C
1	A		80
2	A
3	B
4	B
5	A
6	A
Sheet

 

[maxcorex]

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]

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]

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.