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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484
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)
 

gorhamsj

Board Regular
Joined
Dec 14, 2012
Messages
182
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
 

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484
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?
 

gorhamsj

Board Regular
Joined
Dec 14, 2012
Messages
182

ADVERTISEMENT

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!
 

gorhamsj

Board Regular
Joined
Dec 14, 2012
Messages
182
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!
 

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

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
Top