Doing Different Sum Totals

keepitfrozen

New Member
Joined
Nov 30, 2005
Messages
40
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

New Member
Joined
Mar 26, 2004
Messages
30
keepitfrozen said:
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

New Member
Joined
Nov 30, 2005
Messages
40
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

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

New Member
Joined
Nov 30, 2005
Messages
40
acw said:
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

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

New Member
Joined
Nov 30, 2005
Messages
40
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
keepitfrozen said:
...

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,824
Messages
5,574,525
Members
412,601
Latest member
TheBeaniacExpress
Top