VLOOKUP using dates problem

  • Thread starter Thread starter pstefens
  • Start date Start date
P

pstefens

I've been trying to figure this out for hours, and I know there's a
easy way to figure this out...please help!

I'm using VLOOKUP to try to sum costs from a data range that include
columns such as date (in 01-Jan-04 format), type of expense (Labor
Materials, Overtime), and cost into another range.

I am trying to sort through the data range for all Materials expense
by month. The end result I want is a range with months (Jan, Feb, Mar
etc) as the columns and expenses (L, M, and O) as the rows.

I know I need to nest a function into my VLOOKUP fxn, but I don't kno
how to. I also don't know how to sum those values. Any ideas
 
Hi
if you want to SUM data you may use SUMPRODUCT instead. Lest say your
months are in row one (starting in B1) and your expense type in column
A starting in A2 try the following
- enter the months also as dates (not as text). e.g. enter 1-1-2004 and
format this cell with the custom format "mmm". Otherwise the lookup
won't work!
- enter your expense type in column A (they have to be an exact match
of the text in your other sheet)
- now enter the following formula in B2 (the data for january and your
first material type). Assumptions :
-> col. A on the source sheet = material type
-> col B = Date
-> col C = data to besummed
=SUMPRODUCT(('source_sheet'!$A$1:$A$1000=$A2)*('source_sheet'!$B$1:$B$1
000=B$1),'source_sheet'!$C$1:$C$1000)

copy this formula down and to the right

So this was the formula approach. An even simpler solution would be
using pivot tables :-)
Have a look at
http://www.cpearson.com/excel/pivots.htm

http://peltiertech.com/Excel/Pivots/pivotstart.htm
 
Assuming the dates are in column A, type in B, cost in C.

Then on another sheet, Jan, FeB, in columns B1, C1, etc., Types in A2, A3,
etc.

In B2, put this formula

=SUMPRODUCT((TEXT(Sheet1!A2:A11,"mmm")=TEXT(B$1,"mmm"))*(Sheet1!B2:B11=$A3),
(Sheet1!C2:C11))

and just coipy across and down the grid

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi
forgot the month conversion:
use
=SUMPRODUCT(('source_sheet'!$A$1:$A$1000=$A2)*(MONTH('source_sheet'!$B$
1:$B$1
000)=MONTH(B$1)),'source_sheet'!$C$1:$C$1000)
 
Okay, Thanks, I've tried it but I think there's a few snags...

I'm still having problems with the dates. Why is the sumproduct th
best option to use here? And how does it pull only the labor cost
from January into the table? I think if I understand why I'll be abl
to figure out the bug here.

Also, I changed my dates to the mmm-yyyy format but I'm not sure why
did that.

Sorry if I'm a little slower than the rest...but here's what I wante
to do if I wasn't clear.

Data table:

Date Expense Cost
3 Jan 04 L $1,000
15 Jan 04 O $500
21 Jan 04 O $425
2 Feb 04 L $750

Summary table as a result of the function I'm trying to create:

Jan Feb Mar etc.
L $1,000 $750
O $925 $0

How do I do it?? Thanks
 
Bob and Frank,

Thanks alot, it is now working with all of your help. Just out o
curiosity, is there a way to sum the outputs using VLOOKUPs
 
No, VLOOKUP just returns a value from a table that matches the criteria. You
still need some SUM functionality.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top