Question about adding values using vlookup!!! thanks whomever can help me appreciate it!!

gorhamsj

Board Regular
Joined
Dec 14, 2012
Messages
182
HI guys! how is everyone. I have not been here for a while. I am curious to see if possibly anyone can help me with my predicament.

Ok so in one sheet I have dates going accross and I have line items...

1/1/2014 1/2/2014 1/3/2014
Bed 5 3 8
Cat
Dog 1 2 3
Rat


etc...
:)

My question is I would like to call a week....
so say vlookup the week of 1/1/2014.... and see what are the total expenses for each item..

So in the end you will see a list of the things which are expensed and total expenses (for 7 days).
So in one cell you will see: and in other:
Bed 16
Dog 6


the other ones will be ignored..... hope this is possible.. brownie points for whomever can solve this...
appreciate it.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
You can use SUMPRODUCT to sum a date range. You will manually have to alter the ranges though, not sure how to automate it weekly.


modify to yours
=SUMPRODUCT(--(GL!$B$10:$B36>=DATE(YEAR(TODAY()),1,1)),--(GL!$B$10:$B36<=DATE(YEAR(TODAY()),3,31)),GL!$H$10:$H36)
 
Upvote 0
Thanks a lot shyy!! really.. I am confused.. hahah
I appreciate it... I am trying to figure out the formula
I was thinking.. maybe there can be two cells
one which shows the product for the prices and the other one which calculates the total cost for that week... so if I write in the input cell first of january... there will be a vlookup for that product and the first of jan and add the 7 cells after....

How did you use your formula? thanks
 
Upvote 0
Unfortunately the formula won;t work in your case due to your format. I should have looked at it closer.
Would you be open to change the format?
 
Upvote 0
Yes whatever you recommend... thanks for your time. I am very grateful for your knoweldge...
We can first have all the products in on column so I guess I wont need a formula for those
then it can search the date.... and add the 7 days after...

So it will look like

Bed 555
Cat 0
Dog 222
Rat 0

Then later.. I can try to figure out a formula which eliminates the categories which don't have anything.. hahahaha... ahh the fun of working with excel when I am not good at it!!!

Bed 555
Dog 222
etc...

Thanks for your help.. I owe you!
 
Upvote 0
Yes whatever you recommend... thanks for your time. I am very grateful for your knoweldge...
We can first have all the products in on column so I guess I wont need a formula for those
then it can search the date.... and add the 7 days after...

So it will look like

Bed 555
Cat 0
Dog 222
Rat 0

Then later.. I can try to figure out a formula which eliminates the categories which don't have anything.. hahahaha... ahh the fun of working with excel when I am not good at it!!!

Bed 555
Dog 222
etc...

Thanks for your help.. I owe you!
 
Upvote 0
Just to give you an idea, I add +7 to try to automate the week. But in this example I summed the dates 1/1 - 2/5

Excel 2010
ABCDEFG
1Start DateEnd Date
21-Jan8-JanBed5Bed15
31-Jan8-JanDog2Dog6
415-Jan22-JanBed5
515-Jan22-JanDog2
629-Jan5-FebBed5
729-Jan5-FebDog2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
B2=A2+7
B3=B2
G2=SUMPRODUCT(--($C$2:$C$7=F2),--($A$2:$A$7>=A2),--($B$2:$B$7<=B7),$D$2:$D$7)
G3=SUMPRODUCT(--($C$2:$C$7=F3),--($A$2:$A$7>=A3),--($B$2:$B$7<=B7),$D$2:$D$7)
A4=B2+7
B4=A4+7
A5=A4
B5=B4
A6=B4+7
B6=A6+7
A7=A6
B7=B6

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top