Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif that returns specific limited/rolling range
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, again on a rolling basis? In other words, on the 21st it returns 11, on 22nd 9, on the 23rd 8 and so on. 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 5 E 11/20 Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif that returns specific limited/rolling range
Hi,
If there are no duplicate dates in the range try this with your date in C1 =SUMPRODUCT((A1:A13=C1)*(B1:B13)+(A1:A13=C1-1)*(B1:B13)) Mike "David G." wrote: 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, again on a rolling basis? In other words, on the 21st it returns 11, on 22nd 9, on the 23rd 8 and so on. 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 5 E 11/20 Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif that returns specific limited/rolling range
This will add the B value corresponding to the date entered in C1 plus the B
cell above =INDEX(B:B,MATCH(C1,A:A))+INDEX(B:B,MATCH(C1,A:A)-1) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "David G." wrote in message ... 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, again on a rolling basis? In other words, on the 21st it returns 11, on 22nd 9, on the 23rd 8 and so on. 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 5 E 11/20 Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif that returns specific limited/rolling range
=INDEX(B:B,MATCH(E1,A:A,0))+IF(MATCH(E1,A:A,0)1,I NDEX(B:B,MATCH(E1-1,A:A,0)),0)
-- __________________________________ HTH Bob "David G." wrote in message ... 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, again on a rolling basis? In other words, on the 21st it returns 11, on 22nd 9, on the 23rd 8 and so on. 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 5 E 11/20 Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif that returns specific limited/rolling range
Well done Bob, I was sloppy in not thinking about the top value being in row
1! best wishes -- Bernard "Bob Phillips" wrote in message ... =INDEX(B:B,MATCH(E1,A:A,0))+IF(MATCH(E1,A:A,0)1,I NDEX(B:B,MATCH(E1-1,A:A,0)),0) -- __________________________________ HTH Bob "David G." wrote in message ... 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, again on a rolling basis? In other words, on the 21st it returns 11, on 22nd 9, on the 23rd 8 and so on. 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 5 E 11/20 Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limited data range | Excel Discussion (Misc queries) | |||
Limited Sumif | Excel Discussion (Misc queries) | |||
Range limited by a wildcard | Excel Discussion (Misc queries) | |||
Sumif range returns #NUM! | Excel Worksheet Functions | |||
Sumif for lowerst value within limited to 2 numbers only | Excel Worksheet Functions |