Another SUMIF and VLOOKUP question

[Opie82]

Hello,
We have a construction company with 50+ jobs where we track costs unde
different cost codes (i.e. plumbing, roofing). I can download data wit
totals for each cost code (i.e. the software has already summarized al
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 b
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 i
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, And

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

Hello,
We have a construction company with 50+ jobs where we track costs unde
different cost codes (i.e. plumbing, roofing). I can download data wit
totals for each cost code (i.e. the software has already summarized al
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 b
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 i
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 o
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 thing
easy to adjust to the individual job.

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

S

+-------------------------------------------------------------------
|Filename: Opie82 Example.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=587
+-------------------------------------------------------------------

 

[Opie82]

I think this is what you mean.

I've included a SUMPRODUCT formula that will work in any version o
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 thing
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 kne
where each job was located. But I have downloaded data that is a lis
of costs for 50+ jobs. So I think I need a VLOOKUP function to mak
sure the job # matches first.

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

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

Thanks, And

+-------------------------------------------------------------------
|Filename: Job cost lookup data.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=589
+-------------------------------------------------------------------

 

[Spencer101]

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

I've attached a partial example of the database and the summary where
want to pull the results to. There are 5 different sample jobs shown o
the data page (out of 50+). Only the green highlighted lines meet th
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 th
values are numbers and added the extra condition to the formula
provided before.

Is that of any use to you?

Worth noting that you have two jobs with the number 1003 so that valu
gets duplicated in the first and last job column

+-------------------------------------------------------------------
|Filename: Job cost lookup data.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=591
+-------------------------------------------------------------------

 

[Opie82]

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 th
values are numbers and added the extra condition to the formula
provided before.

Is that of any use to you?

Worth noting that you have two jobs with the number 1003 so that valu
gets duplicated in the first and last job column.

Spencer, that worked!! Thank you!! And

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Spencer101]

Spencer, that worked!! Thank you!! Andy

Pleasure was all mine, Andy. Happy to help.
Thanks for the feedback.:

+-------------------------------------------------------------------
+-------------------------------------------------------------------