Doing Different Sum Totals

[keepitfrozen]

I know i'll probably get flamed for asking such an elementry question but let me give you an example of what i have and then say what i want.

I have 2 columns of data, just say A and B.
Column A has GL codes ending with 30- 31- 32- 40- 41- 42- ect.
Column B has dollar values assocaited with each GL code.

I want to run a formula that only sum totals the values assocaited with lets say the 30- GL code.

I've tried the sumif function and i know nothing about VB coding (yet) but i'm sure this can be done with a function..


Short cut key question as well:
If i want to select all of my data isnt there a short cut key for doing this?
Currently i'm selecting the header row then Shift + Page Down to make the selection but this takes to much time with some of my spreadsheets.

Also is it possible with a shortcut key to edit a cell without having to double click in the cell first?


Sorrry for the newbie questions but did some searches and didnt locate the answer.


Thanks,

Mark

 

[acw]

Mark

1) try =SUMPRODUCT(--(RIGHT(A1:A4,2)="30"),(B1:B4))
2) Try shift, ctrl, down arrow This will take you to the bottom of the current column (assuming no blank cells). You can then go shift, ctrl, right arrow to get to the right boundary.

Another alternative is to go ctrl, end. You may have to move the cursor to put onto the required last cell, then do shift, ctrl, home. This will select from A1 to the required last position.

3) Select the cell, then put the cursor into the formula bar with the mouse and start modifying. This may also depend on how you are setup. Go tools, options, edit and see if you have the edit directly in cell selected.


HTH

Tony

 

[guppas]

If i want to select all of my data isnt there a short cut key for doing this?
Currently i'm selecting the header row then Shift + Page Down . . .

You might try Edit-Goto-Special-Current Region. You cursor has to be in the data of course. You can also add an icon to the bars at the top. It's under "edit", and is much simpler than the previous stuff. Have fun! :wink:

 

[keepitfrozen]

Thanks Tony your the man.

Can i ask you to break down the formula a little bit for me so i can understand how it works a little.

=SUMPRODUCT(--(RIGHT(A1:A4,2)="30"),(B1:B4))

Like how does the formula know to look only at the last 2 characters, perhaps the #2 next to A1:A4?

I'm assuming Right indicates what column to sum?

What do the -- do for the forumla?

I'll try the selection tips once i get my laptop booted up after dinner, thanks.

When i asked about editing the cell, i meant is there any way you can do it without touching the mouse? I"ll check that setting first thou.

Thanks again for you quick response to my questions.

Mark


Edit: Thanks guppas i'll give that a try.

 

[acw]

Mark

The RIGHT function takes the rightmost number of characters from the selected cell. The number determines the number of characters to take. So RIGHT(A1,2) takes the rightmost 2 characters from the value in A2.

In this case, it is checking the rightmost 2 characters for all the cells in the range A1:A4 and testing them against the value 30. 30 has been converted to text, as the right function returns text. This will return a TRUE/FALSE result. The -- is used to convert the TRUE to 1 and the FALSE to 0 (one - makes it negative, the second - makes it positive). This is required by the SUMPRODUCT function which is used to sum the product of 2 ranges.

Effectively, you are checking each value in column A, and if the last 2 numbers are 30, then take the corresponding value in column B. When you have all the values, then add them.

HTH

Tony

 

[keepitfrozen]

Mark

The RIGHT function takes the rightmost number of characters from the selected cell. The number determines the number of characters to take. So RIGHT(A1,2) takes the rightmost 2 characters from the value in A2.

Wouldnt it take the rightmost 2 characters from the value in A1 and not A2 since that is where the reference data is at?

In this case, it is checking the rightmost 2 characters for all the cells in the range A1:A4 and testing them against the value 30. 30 has been converted to text, as the right function returns text. This will return a TRUE/FALSE result. The -- is used to convert the TRUE to 1 and the FALSE to 0 (one - makes it negative, the second - makes it positive). This is required by the SUMPRODUCT function which is used to sum the product of 2 ranges.

I understand most of this except for the True/False portion of that but i'll do some more searches for explainations on this.

Thanks for your time and knowledge Tony

Mark
[/quote]

 

[acw]

Mark

Yep, should be A1. Slip of the fingers....

The true / false is a boolean test. if you have 1 in A1 and 1 in B1, then put
=a1=b1
in C1 and it will return true (try it). Now if you want to convert that to a number, then in D1 enter
=--C1 and it will show a 1.

Put a 2 in B1 and things will go the other way (C1 will be False, and D1 will be 0)


Tony

 

[keepitfrozen]

ahh, now i got you. I understand what your saying now but i think it will be sometime before i fully understand how this applies to function. It makes sense to me how this worked with the sumproduct function but does this work with all functions or just a few?

Man how did you teach your self to think this way.

Mark

 

[Aladin Akyurek]

...

I have 2 columns of data, just say A and B.
Column A has GL codes ending with 30- 31- 32- 40- 41- 42- ect.
Column B has dollar values assocaited with each GL code.

I want to run a formula that only sum totals the values assocaited with lets say the 30- GL code.

I've tried the sumif function...

If GL codes are of text data type (e.g., '234530 entered as such in a cell)...

=SUMIF($A$2:$A$10,"*30",$B$2:$B$10)

or, with F2 housing 30,

=SUMIF($A$2:$A$10,"*"&F2,$B$2:$B$10)

will suffice.

 

[Peter_SSs]

Also is it possible with a shortcut key to edit a cell without having to double click in the cell first?

Once the cell is selected, press the F2 key.