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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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,210
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
54,966
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,835
Messages
5,855,909
Members
431,772
Latest member
dannyboi1

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