I recently hit the 1,024 character limit with a rather simple array formula, and I've run out of ideas to simplify it. The formula adds up all values in 21 worksheets by month and year. To save formula space I created named ranges in each worksheet. For example, the column containing each date in the first worksheet is "Ma" and in the second worksheet is "Mb" and so on; and the column with values to add up is "Ba" and "Bb" etc.
Columns AC and AD include formulas =MONTH(A123) and =YEAR(A123) to eliminate the extra MONTH() and YEAR() in this array formula.
The problem is that this formula is 987 characters. Adding the 22nd worksheet with named ranges "Mv" and "Bv" pushes the formula to 1,034 characters, so currently I'm limited to 21 worksheets. I know I could move columns AC and AD to A and B, but this only saves enough characters to increase the formula's limit to 22 worksheets.{=SUM(IF((MONTH(Ma)=AC123)*(YEAR(Ma)=AD123),Ba)) +SUM(IF((MONTH(Mb)=AC123)*(YEAR(Mb)=AD123),Bb)) +SUM(IF((MONTH(Mc)=AC123)*(YEAR(Mc)=AD123),Bc)) +SUM(IF((MONTH(Md)=AC123)*(YEAR(Md)=AD123),Bd)) +SUM(IF((MONTH(Me)=AC123)*(YEAR(Me)=AD123),Be)) +SUM(IF((MONTH(Mf)=AC123)*(YEAR(Mf)=AD123),Bf)) +SUM(IF((MONTH(Mg)=AC123)*(YEAR(Mg)=AD123),Bg)) +SUM(IF((MONTH(Mh)=AC123)*(YEAR(Mh)=AD123),Bh)) +SUM(IF((MONTH(Mi)=AC123)*(YEAR(Mi)=AD123),Bi)) +SUM(IF((MONTH(Mj)=AC123)*(YEAR(Mj)=AD123),Bj)) +SUM(IF((MONTH(Mk)=AC123)*(YEAR(Mk)=AD123),Bk)) +SUM(IF((MONTH(Ml)=AC123)*(YEAR(Ml)=AD123),Bl)) +SUM(IF((MONTH(Mm)=AC123)*(YEAR(Mm)=AD123),Bm)) +SUM(IF((MONTH(Mn)=AC123)*(YEAR(Mn)=AD123),Bn)) +SUM(IF((MONTH(Mo)=AC123)*(YEAR(Mo)=AD123),Bo)) +SUM(IF((MONTH(Mp)=AC123)*(YEAR(Mp)=AD123),Bp)) +SUM(IF((MONTH(Mq)=AC123)*(YEAR(Mq)=AD123),Bq)) +SUM(IF((MONTH(Mr)=AC123)*(YEAR(Mr)=AD123),Br)) +SUM(IF((MONTH(Ms)=AC123)*(YEAR(Ms)=AD123),Bs)) +SUM(IF((MONTH(Mt)=AC123)*(YEAR(Mt)=AD123),Bt)) +SUM(IF((MONTH(Mu)=AC123)*(YEAR(Mu)=AD123),Bu))}
Does anyone know of another method to make this work that would allow me to use up to 25 worksheets, or possibly more? Using a SUM(IF(...)) array formula is the only technique I'm familiar with that can produce the desired result when the source data's date range spans more than one year.
Last edited by joe4444; 03-31-2010 at 08:24 PM.
Would it be possible to put each one of those SUM formulas in cells on a different sheet, for example, a "Calculation" sheet with each SUM in A1 through A22, then sum the total at the bottom, then on your other page have =Calculation!A23
This link might be helpful: Conditional Sum Across Worksheets
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.
Thanks for the quick replies.
I think I didn't provide enough detail about this array formula. The "summary" worksheet contains dates from January 2000 through December 2009, and the code in my original post is only for the last cell corresponding to December 2009 on row 123. The array formula is in rows 4 through 123, each row corresponding to a month in that range. All 21 of the individual worksheets have a similar structure with dates, but they do not necessarily include all dates from Jan-00 to Dec-09.
What may also be confusing is the {=SUM(IF(...))} part. Although the formula is technically adding up numerous cells, the two conditions -- MONTH(Ma)=AC123 and YEAR(Ma)=AD123 -- eliminate, or zero, all cells except one in the corresponding range Ba: the one cell that matches both month and year (e.g. March 2003). Effectively this is a two-way lookup. What makes it different from a "normal" two-way lookup is that both conditions (month and year) are in a single column of dates, which is why I'm using the array formula.
Does that make sense? If not I will create a sample workbook that should clarify the situation.
You could ...
if A123 is a date and if Ma is a range of date values, you could put in AD123
=text(A123,"mm/yy")
and then use in your array
{=SUM(IF(text(Ma,"mm/yy")=AD123),Ba))
That will gain a few characters.
On the other hand, you could on each sheet designate one cell to perform the two way lookup based on Sheet1!AD123 and then do a 3D sum of all sheets.
teylyn,
I like your first idea. It can actually gain up to 15 characters this way:
{=SUM(IF(TEXT(Ma,"my")=AC123,Ba))+...+SUM(IF(TEXT(Mad,"my")=AC123,Bae))}.
That would allow up to 31 worksheets with 1,002 characters. I really doubt I'll ever need more than 30 but I know of a few instances when 20 falls short, so this solves my problem!
Still, I'm curious about your second suggestion. By 3D sum do you mean something like =SUM(Sheet1:Sheet30!A1)?
If so, that wouldn't work in my case. Every sheet starts with all rows for Jan-00 to Dec-09, but in each individual sheet I have to delete the rows it doesn't use. For example, if I only need Mar-03 to Feb-07 then I delete all rows above and below that range. Another sheet may need a different date range. When rows are deleted 3D sum will produce unwanted results (without warning), which is why I use an array formula.
Glad you found something that worked.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
I took the idea a little further, and with additional intermediate formulas an array formula is no longer necessary. Plus with even fewer characters required I can use more understandable named ranges.
Thanks again!=SUMIF(Months1,AC123,Balance1) +SUMIF(Months2,AC123,Balance2) ... +SUMIF(Months30,AC123,Balance30)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks