Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default Another SUMIF and VLOOKUP question

Hello,
We have a construction company with 50+ jobs where we track costs under different cost codes (i.e. plumbing, roofing). I can download data with totals for each cost code (i.e. the software has already summarized all transactions by cost code).

For each job I have up to 50 costs codes which range from 1000 to 9000. Each home will likely use completely different cost codes. I want to be able to get a total by job for just a specific range of cost codes (i.e. 4200 thru 4700)

For the example below, I want to add the codes 4300 and 4450. What is the best way to get this info?

Job Cost Code Amount
1001 1000 $2,000.00
1001 2500 $ 300.00
1001 4000 $ 600.00
1001 4300 $1,000.00
1001 4450 $ 800.00
1001 6000 $6,000.00
thru 1050

Thanks, Andy
  #2   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Opie82 View Post
Hello,
We have a construction company with 50+ jobs where we track costs under different cost codes (i.e. plumbing, roofing). I can download data with totals for each cost code (i.e. the software has already summarized all transactions by cost code).

For each job I have up to 50 costs codes which range from 1000 to 9000. Each home will likely use completely different cost codes. I want to be able to get a total by job for just a specific range of cost codes (i.e. 4200 thru 4700)

For the example below, I want to add the codes 4300 and 4450. What is the best way to get this info?

Job Cost Code Amount
1001 1000 $2,000.00
1001 2500 $ 300.00
1001 4000 $ 600.00
1001 4300 $1,000.00
1001 4450 $ 800.00
1001 6000 $6,000.00
thru 1050

Thanks, Andy
I think this is what you mean.

I've included a SUMPRODUCT formula that will work in any version of Excel and a SUMIFS one that will work in 2007 or later.

There's a cell for the start and end of cost code range to make things easy to adjust to the individual job.

Let me know if that's not what you meant at all.

S.
Attached Files
File Type: zip Opie82 Example.zip (5.9 KB, 184 views)
  #3   Report Post  
Junior Member
 
Posts: 3
Default

Quote:
Originally Posted by Spencer101 View Post
I think this is what you mean.

I've included a SUMPRODUCT formula that will work in any version of Excel and a SUMIFS one that will work in 2007 or later.

There's a cell for the start and end of cost code range to make things easy to adjust to the individual job.

Let me know if that's not what you meant at all.

S.
Thanks S. I think that would work if I just had one job, or I knew where each job was located. But I have downloaded data that is a list of costs for 50+ jobs. So I think I need a VLOOKUP function to make sure the job # matches first.

I've attached a partial example of the database and the summary where I want to pull the results to. There are 5 different sample jobs shown on the data page (out of 50+). Only the green highlighted lines meet the criteria.

I want the answers to come to the summary tab going across columns. Unfortunately I'm working with the 2003 version.

Thanks, Andy
Attached Files
File Type: zip Job cost lookup data.zip (6.8 KB, 188 views)
  #4   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Opie82 View Post
Thanks S. I think that would work if I just had one job, or I knew where each job was located. But I have downloaded data that is a list of costs for 50+ jobs. So I think I need a VLOOKUP function to make sure the job # matches first.

I've attached a partial example of the database and the summary where I want to pull the results to. There are 5 different sample jobs shown on the data page (out of 50+). Only the green highlighted lines meet the criteria.

I want the answers to come to the summary tab going across columns. Unfortunately I'm working with the 2003 version.

Thanks, Andy
You can accomplish that by adding another condition to the SUMPRODUCT.

Have a look at the attached now.

I changed the formatting on Column E of the "JC data" worksheet so the values are numbers and added the extra condition to the formula I provided before.

Is that of any use to you?

Worth noting that you have two jobs with the number 1003 so that value gets duplicated in the first and last job column.
Attached Files
File Type: zip Job cost lookup data.zip (7.7 KB, 155 views)
  #5   Report Post  
Junior Member
 
Posts: 3
Default

Quote:
Originally Posted by Spencer101 View Post
You can accomplish that by adding another condition to the SUMPRODUCT.

Have a look at the attached now.

I changed the formatting on Column E of the "JC data" worksheet so the values are numbers and added the extra condition to the formula I provided before.

Is that of any use to you?

Worth noting that you have two jobs with the number 1003 so that value gets duplicated in the first and last job column.
Spencer, that worked!! Thank you!! Andy


  #6   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Opie82 View Post
Spencer, that worked!! Thank you!! Andy
Pleasure was all mine, Andy. Happy to help.
Thanks for the feedback.:)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP & SUMIF Jock Excel Worksheet Functions 7 April 2nd 23 07:53 PM
SumIf and Vlookup together? cmillls45 Excel Worksheet Functions 0 September 14th 12 04:13 PM
Sumif or vlookup dejavu34 Excel Worksheet Functions 1 January 31st 12 02:45 AM
Vlookup + sumif , but how???? Jessejames Excel Worksheet Functions 4 April 21st 10 06:41 PM
sumif or vlookup help Katie Excel Worksheet Functions 2 July 2nd 08 05:21 PM


All times are GMT +1. The time now is 03:16 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"