Help with an array function
okay, I've got a worksheet (Individual Week Totals Tracking) that uses an array function to call on another worksheet (Lantigua) and sums the cells in each column holding a specific date in the Lantigua worksheet. Within the Lantigua worksheet are weeks of the month starting with saturdays (i.e. week of 4-June) and within each week holds values which are totaled at the end of each group of cells. In the totals section there is an IF statement to display no text if there are no values in that week, and adds all the values if there is ANY values entered. When the array formula calls the Lantigua worksheet, it adds the total values of the whole month given the criterea of the column holding all the days of the corresponding month. And it works when there are values entered in EVERY week of the month. But if there are no values entered for an entire week, the array formula will read "#Value!". Is this because of the IF statement? Here are the formulas: INDIVIDUAL WEEK TOTALS TRACKING {=SUM((Lantigua!$C$2:$HJ$2=DATEVALUE("1-Jan"))*(Lantigua!$C$2:$HJ$2<=DATEVALUE("31-Jan"))*(Lantigua!$C$5:$HJ$5))} LANTIGUA =IF(C5+D5+E5+F5+G5+H5+I5<=0,"",SUM(C5:I5)) -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=378044 |
Its probably because you are entering a "" in the if statement. Try entering 0 instead. Another way would be to evaluate your array formula with an IF statement and checking its result if it errors out. If yes, enter "", else the actual formula. Infact you could use the same if in your array formula, something like: =IF(C5+D5+E5+F5+G5+H5+I5<=0,"",your_array_formula) Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=378044 |
The reason I have the "" in the IF statement is because I don't want the entire worksheet filled with "0". If there is no data for say the month of July cause it has not reached July, I don't want the cells to read "0", I want them to read nothing at all, for neatness and clarity when using and printing the spreadsheet. Is there a way I can keep that sum array formula and omit certain cells from being added? For example: {=SUM((Lantigua!$C$2:$HJ$2=DATEVALUE("1-Jan"))*(Lantigua!$C$2:$HJ$2<=DATEVALUE("31-Jan"))*(Lantigua!$C$5:$HJ$5))} Omitting cells J5,R5,Z5,AH5,AP5,AX5,BF5,BN5,BV5,CD5,CL5,CT5,DB5,D J5,DR5,DZ5,EH5,EP5,EX5,FF5,FN5,FV5,GD5,GL5,GT5,HB5 ,HJ5 These are the cells that contain the IF statement, I figure if I can omit those cells from being summed up, the array formula will work fine. -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=378044 |
As I said, the easiest way out for you would be to evaluate the result of the array formula. As I don't know what exactly is happening, I can only suggest this method: =IF(ISNUMBER(SUM((Lantigua!$C$2:$HJ$2=DATEVALUE(" 1-Jan"))*(Lantigua!$C$2:$HJ$2<=DATEVALUE("31-Jan"))*(Lantigua!$C$5:$HJ$5))),SUM((Lantigua!$C$2: $HJ$2=DATEVALUE("1-Jan"))*(Lantigua!$C$2:$HJ$2<=DATEVALUE("31-Jan"))*(Lantigua!$C$5:$HJ$5)),"") Mangesh -- mangesh_yadav ------------------------------------------------------------------------ mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470 View this thread: http://www.excelforum.com/showthread...hreadid=378044 |
I couldn't get that method to work, but I got it to work. I decided to ditch the IF statements and just conditional format the cells to have white text (or whatever shading the cell may have) if the cell read 0. I guess it's good enough, and now the original array function works. Thanks anyway -- malik641 ------------------------------------------------------------------------ malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190 View this thread: http://www.excelforum.com/showthread...hreadid=378044 |
All times are GMT +1. The time now is 03:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com