Originally I needed an offset function -- I am trying to automatically add up data, excluding that last two data points in a row. And every month add the next data point based on the date of the spreadsheet (top left). Please see trail below... can someone else help? This sting is going in circles.
Thanks -- Keri
Last edited by kdegross; 12-16-2009 at 04:36 PM.
It is using the value in B40 to determine the size of the range to sum based on a condition.
So if B40 is greater than 0, it goes to A12 and offeset by that number in B40.. so if B40 equals 5, then the range is A12:A17 for the first part of the Sumif and B12:B17 for the second part.
It looks for strings in A2:A17 that end with BILL and then sums the values correspondingly in B12:B17.
It would be better to write it as:
=IF(B$40<=0,0,SUMIF($A$12:Index($A:$A,12+B$40),"=*Bill",B$12:Index($B:$B,12+B$40))
as it would not be volatile...
Last edited by NBVC; 12-15-2009 at 02:05 PM.
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.
Please see the explanation of the Excel Help on OFFSET, SUMIF and IF functions.
Briefly:
You have a nested formula starting with the IF function.
IF the argument (B$40<=0) is true then return a result of zero. If it is not true (i.e B40 is > 0) then perform the SUMIF function.
SUMIF nest the OFFSET function twice, but it works the same way in each case. SUMIF is used to test if a critieria is met before summing the values.
OFFSET uses an anchor cell (A$12) and numeric values for how many rows and columns to offset from the anchor and numeric values for how many rows and columns are to be included in the offset range.
The formula uses a reference to cell B$40 as the number of rows to return.
Back to SUMIF. If the range of cells returned by the first OFFSET formula meet the criteria of "=*Bill", then sum the values in the cells returned by the second OFFSET formula (based on B$12 and also referencing B$40 as number of rows to include in the range of rows.
HTH
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
I think ...
=IF(B$40<=0, 0, SUMIF($A$12:INDEX($A:$A, 11 + B$40), "=*Bill", $B$12:INDEX($B:$B, 11 + B$40) ) )
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Thanks that makes sense. When I use your version of the formula it does not add up the correct cells, it adds one more to the range.
I think shg is correct... I neglected to show that it counts from A12, including A12, since the offset rows is 0... so if B40 is 5, then A12:A16 and B12:B16...
shg's formula with 11 instead of 12 would be the right one...
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.
Thanks all, this makes sense. And now allows me to change if needed. Thanks!
I am trying to duplicate this but on the rows... so I exclude the last two data cells (well four actually) based off the date (which I have) but so the function is reading rows instead of the columns. So basically, I want to exclude the last two months of data (Oct and Nov in the attached spreadsheet) so I can find the new total based on just those numbers without having to do it manually every month. Can you help!
Try:
=IF(AC$8<=0,0,SUM(B$4:INDEX(B4:X4,AC$8)))
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.
The number is too high, it should be 238,563
... your thoughts?
Try:
=IF(AC$6<=0,0,SUM(B$6:INDEX($B6:$Y6,AC$6)))
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.
Great so basically if I am looking AC6 if zero (meaning no mature data), return zero. If not, sum that row, but only grab the number of rows indicated in cell AC6 (which is the index function). Correct? Thanks again!
Yes, Correct, but grabbing columns not rows...
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.
Okay now how do I figure out if you go down the columns and notice that depending on the month of data, how many rows to exclude. Those top numbers are totals. But how do I look at the Bill 1-Bill 12, and determine how many data points to pull?
So I am not understanding you... what exactly are your trying to get in that cell? What should the answer be and how did you get it?
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks