Looks Like a Pivot Table But Isn't...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a list of tasks in my worksheet.
example:

cell A1: Category
cell B1: Task
cell C1: QTY Unit
cell D1: Cost ($)
cell E1: Time on Task (HR)

i.e.:
cell A2: framing
cell B2: wall framing
cell C2: 50
cell D2: $75.00
cell E2: 4.5 hr

cell A3: framing
cell B3: roof framing
cell C3: 75
cell D3: $660.00
cell E3: 12.25 hr

cell A4: drywall
cell B4: wall drywall
cell C4: 90
cell D4: $330.00
cell E4: 6.65 hr

I have a separate worksheet with a table containing all the specified categories (framing, drywall, exterior finish, etc.). What I want in this table are the sums of each specific category.
i.e.:

Category | Cost | Time
framing | $165.00 | 16.75h
drywall | $330.00 | 6.65h

The vlookup function only looks up the last "framing" in the original table, it doesn't give me the sum of the cost or time. Is there a way that I can have the sum of all the "framing" variables in the other worksheet table without making a Pivot Table? The reason I ask this is because this table is inserted in a specific formated report sheet.

Thanks,

MCorrea
 
MCorrea, try something like =SUMPRODUCT((A2:A4="framing")*(D2:D4)) for the
cost,
and =SUMPRODUCT((A2:A4="framing")*(E2:E4)) for the time on task.

If the summary portion of your workbook is set up as in your example, then
you can use a formula like

=SUMPRODUCT((Sheet1!$A$2:$A$4=$A2)*(Sheet1!D$2:D$4)) in Cell B2, copying it
down and to the right.

--
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com


MCorrea said:
Hi,

I have a list of tasks in my worksheet.
example:

cell A1: Category
cell B1: Task
cell C1: QTY Unit
cell D1: Cost ($)
cell E1: Time on Task (HR)

i.e.:
cell A2: framing
cell B2: wall framing
cell C2: 50
cell D2: $75.00
cell E2: 4.5 hr

cell A3: framing
cell B3: roof framing
cell C3: 75
cell D3: $660.00
cell E3: 12.25 hr

cell A4: drywall
cell B4: wall drywall
cell C4: 90
cell D4: $330.00
cell E4: 6.65 hr

I have a separate worksheet with a table containing all the specified
Click to expand...
categories (framing, drywall, exterior finish, etc.). What I want in this
table are the sums of each specific category.
i.e.:

Category | Cost | Time
framing | $165.00 | 16.75h
drywall | $330.00 | 6.65h

The vlookup function only looks up the last "framing" in the original
Click to expand...
table, it doesn't give me the sum of the cost or time. Is there a way that I
can have the sum of all the "framing" variables in the other worksheet table
without making a Pivot Table? The reason I ask this is because this table is
inserted in a specific formated report sheet.
 
Hi
just a minor addition: If your time could exceed 24 hours you may
format this cell with the custom format
[hh]:mm
to prevent the rollover after 24 hours
 
Works Great!!!!!!!!

Thank You Very Much..........

MCorrea

Frank Kabel said:
Hi
just a minor addition: If your time could exceed 24 hours you may
format this cell with the custom format
[hh]:mm
to prevent the rollover after 24 hours

--
Regards
Frank Kabel
Frankfurt, Germany

MCorrea, try something like =SUMPRODUCT((A2:A4="framing")*(D2:D4))
for the cost,
and =SUMPRODUCT((A2:A4="framing")*(E2:E4)) for the time on task.

If the summary portion of your workbook is set up as in your example,
then you can use a formula like

=SUMPRODUCT((Sheet1!$A$2:$A$4=$A2)*(Sheet1!D$2:D$4)) in Cell B2,
copying it down and to the right.


specified
categories (framing, drywall, exterior finish, etc.). What I want in
this table are the sums of each specific category. original
table, it doesn't give me the sum of the cost or time. Is there a way
that I can have the sum of all the "framing" variables in the other
worksheet table without making a Pivot Table? The reason I ask this
is because this table is inserted in a specific formated report sheet.
Click to expand...
Click to expand...
 
Back
Top