Excel help here plz - DFWstangs Forums
 
LinkBack Thread Tools Display Modes
post #1 of 5 (permalink) Old 10-28-2009, 09:23 AM Thread Starter
AHMO!
 
Join Date: Apr 2004
Location: the original let me google that for ya guy
Posts: 16,810
Excel help here plz

Ok I have two columns of data approx 17k rows.

One contains a Service location ID, and the other contains a $ amount to be charged.

There are about 5-7 of each Service location ID so I can't just copy a formula down for every 6 or so because some are 5, some are 7.

Basically I want to sum each Service location ID with a forumla, I believe it would be a sumif forumula but I'm not really up to speed on how to write the formula.

Here is a very small example of the data, whomever figures this out I will be greatly appreciative and maybe buy you lunch.

$9.05 006129098
$5.12 006129098
$1.26 006129098
$0.98 006129098
$1.88 006129098
$1.95 006129098
$0.30 006129098
$9.05 006129699
$12.41 006129699
$3.05 006129699
$2.39 006129699
$4.55 006129699
$1.95 006129699
$0.30 006129699
$9.05 006197019
$9.37 006197019
$0.49 006197019
$0.38 006197019
$0.72 006197019
$1.95 006197019
$0.30 006197019
Nate is offline  
Sponsored Links
Advertisement
 
post #2 of 5 (permalink) Old 10-28-2009, 09:53 AM Thread Starter
AHMO!
 
Join Date: Apr 2004
Location: the original let me google that for ya guy
Posts: 16,810
N/M I was an idiot, used the subtotal function, god damn I love Excel 07
Nate is offline  
post #3 of 5 (permalink) Old 10-28-2009, 09:58 AM
Aspiring Bean Counter.
 
Slowhand's Avatar
 
Join Date: Feb 2006
Location: Howard Johnson's Earthlight Room
Posts: 12,279
You're on the right track. Create cells to the side of the table that have all of the different values in them. Well call that cell C2 for now.

C2 would have the data "006129098"

Formula should be:

SUMIF(Range of cells of service ID, C2 (the criteria you want it to meet), Range of cells for dollar amount)

So if all of you service cells were in column A starting at A2 and your dollar amounts were in column B starting at B2, you would have:

SUMIF(A2:A17000, C2, B2:B17000)

I hope that makes sense and I hope it works.

Slowhand is offline  
 
post #4 of 5 (permalink) Old 10-28-2009, 09:58 AM
Aspiring Bean Counter.
 
Slowhand's Avatar
 
Join Date: Feb 2006
Location: Howard Johnson's Earthlight Room
Posts: 12,279
Quote:
Originally Posted by Nate View Post
N/M I was an idiot, used the subtotal function, god damn I love Excel 07
Well shit. Glad you figured it out! That's way easier than what I just tried to tell you.


Last edited by Slowhand; 10-28-2009 at 10:12 AM.
Slowhand is offline  
post #5 of 5 (permalink) Old 10-28-2009, 10:05 AM Thread Starter
AHMO!
 
Join Date: Apr 2004
Location: the original let me google that for ya guy
Posts: 16,810
Quote:
Originally Posted by Slowhand View Post
You're on the right track. Create cells to the side of the table that have all of the different values in them. Well call that cell C2 for now.

C2 would have the data "006129098"

Formula should be:

SUMIF(Range of cells of service ID, C2 (the criteria you want it to meet), Range of cells for dollar amount)

So if all of you service cells were in column A starting at A2 and your dollar amounts were in column B starting at B2, you would have:

SUMIF(A2:A17000, C2, B2:B17000)

I hope that makes sense and I hope it works.
Quote:
Originally Posted by Slowhand View Post
Well shit. Glad you figure it out! That's way easier than what I just tried to tell you.
This is exactly what I was trying to do until I realized...hey there's this function called fucking subtotal...wonder what the hell that does.
Nate is offline  
Sponsored Links
Advertisement
 
Reply

Bookmarks

Quick Reply
Message:
Options

Register Now



In order to be able to post messages on the DFWstangs Forums forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.

User Name:
Password
Please enter a password for your user account. Note that passwords are case-sensitive.

Password:


Confirm Password:
Email Address
Please enter a valid email address for yourself.

Email Address:
OR

Log-in










Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page
Display Modes
Linear Mode Linear Mode



Posting Rules  
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are On
Pingbacks are On
Refbacks are On

 
For the best viewing experience please update your browser to Google Chrome