Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Xlookup that returns variable sum data
I am trying to create a formula that looks for todays date in a table and
returns the sum of daily entries for the month prior to the date. In the sample below =vlookup(E1,A1:C2,2) instead of returning 6 value from row #2, it will return 11, the sum of all values prior to the one assigned to the date. Furthermore, can I set it to change the range tomorrow, adding yet another cell to the sum for a return of 14? 1 2 A 11/20 5 B 11/21 6 C 11/22 3 D E 11/20 Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Xlookup that returns variable sum data
Hi,
take your criteria out of the column then sum the entire column based on the criteria =SUMIF(A:A,"<="&C1,B:B) Sums all coulm B for a date in column A <= C1 Mike "David G." wrote: I am trying to create a formula that looks for todays date in a table and returns the sum of daily entries for the month prior to the date. In the sample below =vlookup(E1,A1:C2,2) instead of returning 6 value from row #2, it will return 11, the sum of all values prior to the one assigned to the date. Furthermore, can I set it to change the range tomorrow, adding yet another cell to the sum for a return of 14? 1 2 A 11/20 5 B 11/21 6 C 11/22 3 D E 11/20 Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Xlookup that returns variable sum data
Hi David,
I am confuse here, if todays date is 11/20 the result of you formula never can be 11 since you are looking the sum of all the entries prior to that date. let's say in column A row 1 you have the Today date, then in column A row 4 you have different dates all the way down and in column B you have the values. Do this In column B add a formula =A1-30. This will calculate todays day minus 30 days Then in the cell you want to summarize the movement for the prior 30 days enter =SUMPRODUCT(--(A:A<A1),--(A:A=B1),--B:B) "David G." wrote: I am trying to create a formula that looks for todays date in a table and returns the sum of daily entries for the month prior to the date. In the sample below =vlookup(E1,A1:C2,2) instead of returning 6 value from row #2, it will return 11, the sum of all values prior to the one assigned to the date. Furthermore, can I set it to change the range tomorrow, adding yet another cell to the sum for a return of 14? 1 2 A 11/20 5 B 11/21 6 C 11/22 3 D E 11/20 Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Xlookup that returns variable sum data
Mike,
It worked perfectly. How about if I need to add not the entire column, but the one corresponding to today's date and the previous cell only? Can that be done? Thanks! "Mike H" wrote: Hi, take your criteria out of the column then sum the entire column based on the criteria =SUMIF(A:A,"<="&C1,B:B) Sums all coulm B for a date in column A <= C1 Mike "David G." wrote: I am trying to create a formula that looks for todays date in a table and returns the sum of daily entries for the month prior to the date. In the sample below =vlookup(E1,A1:C2,2) instead of returning 6 value from row #2, it will return 11, the sum of all values prior to the one assigned to the date. Furthermore, can I set it to change the range tomorrow, adding yet another cell to the sum for a return of 14? 1 2 A 11/20 5 B 11/21 6 C 11/22 3 D 11/23 6 E 11/20 Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date returns always returns: 00 January 1900 | Excel Worksheet Functions | |||
Vlookup Returns Wrong/No Data | Excel Worksheet Functions | |||
vlookup returns bad data | Excel Worksheet Functions | |||
How do I get multiple data returns from one drop down box? | Excel Discussion (Misc queries) | |||
VLOOKUP Returns Erroneous Value When Control Data is Variable | Excel Worksheet Functions |