Thanks for your quick response! So basicallly I put next to my botched formula what the answers should be for each row... I want to add the total each row month cancels, but exclude the last 3 data points because they are not mature.
The result you show of 12,869 is the sum up to column L...don't you want to column Q which would be 16 columns?
If so,
=IF(AC$17<=0,0,SUMIF($B16:INDEX($B16:AA16,$AC$17),"<>",$B17:INDEX($B17:AA17,$AC$17)))
gets 13,621.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
No I don't want Column Q. Column Q is a percentage. I am adding the numbers. For each row (17,19,21,23,etc) I want to add up the cancels (numbers under the month column) and subtract out the last two data points. So each row will be different.
Row 17 -- would B,D,F,H,J and then next month would add L to the mix and so on.
Row 19 would add B,D,F,H and then next month would add J to the mix and so on.
Row 21 would add B,D,F and next month add H and so on.
When I say next month I mean with the date change at the top of the page.
Does that make sense?
Well you have merged column titles.. really I meant column P
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
No I don't want P, it is not mature... I want as many data points for each row - two data points.
Row 17 -- would B,D,F,H,J and then next month would add L to the mix and so on.
Row 19 would add B,D,F,H and then next month would add J to the mix and so on.
Row 21 would add B,D,F and next month add H and so on.
Then your "columns to grab" for row 17 is 12, not 16
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I understand that, but I don't know how to change that to make it automatic. That is the whole point of this string...I want it to automatically update every month -- how many rows to grab.
Did you look at the spreadsheet I attached?
I used this to get columns to pull:
=MATCH(9.999999E+307,B16:Y16)-1
and this to get sums
you can copy down and delete the intermediate rows...
I am not sure if the last 2 don't match the same logic of avoiding last 3 dates... but I get different number than you.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
You didn't include the second formula?
=if(ac$17<=0,0,sumif($b16:index($b16:aa16,$ac17-4),"<>",$b17:index($b17:aa17,$ac17-4)))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
This works. Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks