Formula help
Posted by David on June 05, 2000 12:25 AM
=SUM(INDEX($A10:$AV1770,MATCH($A$2,$A10:$A1770,0),2):INDEX($A10:$AV1770,MATCH($A$3,$A10:$A1770,0),2))
a2 and a3 are dates.
I need to sum up the numbers in the columns to the right of the date.
so cell equals all the valuse in column b between two dates in column a.
Trying not to make a new formula because I use this formula a lot of times to sum up the values in many rows between two dates. Hope this helps someone cause its got me stumped. I tried using a -1 and 1 instead of the 0's but that i couldn't get to work either.
If one of the dates is not on the sheet then it gives me an error message. What i need it to do is to take the date in a2 or the next higher date and the date in a3 or the next lower date.
Posted by JAF on June 05, 0100 1:35 AM
Posted by David on June 06, 0100 12:39 AM
Appreciate the time but I gues i realy didn't explain my problem well.
=SUM(INDEX($A10:$AV1770,MATCH($A$2,$A10:$A1770,0),2):INDEX($A10:$AV1770,MATCH($A$3,$A10:$A1770,0),2))
a2 and a3 are two dates
I need to take those two dates and find them in the column a(which is in order by oldest date at the top. I then need to go to another row and add up the dates between those two dates. I realy would like not to change my formula because i use it in over thirty sheets. I tried looking at the match function and changing the zero's to -1 and +1 but it did not have much success. Hope that explains my problem a little better.
Posted by Celia on June 06, 0100 4:31 AM
David
There's probably a better way, but I think the following does it.
Enter the following formula in cell B10 and drag it down to B1770 :-
=CELL("address",A10)
The following formula should then provide what you need :-
=SUM(OFFSET(INDIRECT(VLOOKUP($A$2,$A10:$B1770,2)),1,0):OFFSET(INDIRECT(VLOOKUP($A$3,$A10:$B1770,2)),-1,0))
Celia
Posted by Celia on June 06, 0100 5:29 PM
David
Have been able to come up with a formula that avoids having to use column B.
I've posted this question on another message board and will advise if a solution is received.
Celia
Posted by Celia on June 06, 0100 5:31 PM
:
Correction :-
I have been UNable ........
Posted by Celia on June 07, 0100 1:04 AM
David
I should have checked with you before, but do you really want to sum the dates(which seems a bit unusual) or do you want to count the number of dates? (Or maybe get the number of days difference between the two dates?)
Celia
Posted by Celia on June 07, 0100 1:37 AM
David
Igmore all of my previous messages. I didn't look at your formula properly, but have done so now.
I think the following formula will do it :-
=SUM(INDIRECT(CELL("address",OFFSET(INDEX($A10:$AV1770,MATCH($A$2,$A10:$A1770,0),2),1,0))):INDIRECT(CELL("address",OFFSET(INDEX($A10:$AV1770,MATCH($A$3,$A10:$A1770,0),2),-1,0))))
Celia
Posted by david on June 07, 0100 8:33 PM
sorry a2 and a3 are the dates then there is a column of dates in a10 through a1770
then i want to total other column b,c,d,e,f,g,h,i,j,k,l,and m. So a total of all column b between days a2 and a3. Now this is the formula i use know and it works good except i am making a sheet to sumarize data on many dif sheets in man dif workbooks and the 2 dates in a2 and a3 i would like to be the same. and if one of the dates is not on one of the sheets i get an error message instead of taking the next larger date for the first and the next smaller date on the last.
I have this formula used probably over 800 times and would like to not have it change much if that is possible or maybe if i find a better formula to build a macro to find my old formula and change it to a new one. Pray to the excel gods for me.
Posted by david on June 07, 0100 8:50 PM
David
sorry missed this response but that formula doesn't work right either. If i enter 6/5 and 6/6 it gives me the totals from 6/3 to 6/6. I dont have a 6/4 or a 6/7 on the list. If i enter 6/7 it gives me an error. I want to be able to enter 6/4 and 6/7 and since neither of those dates are on the list i want it to give me the values for the dates 6/5 and 6/6 from the different column.
