Help with an array function

[malik641]

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))

 

[mangesh_yadav]

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

 

[malik641]

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,DJ5,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.

 

[mangesh_yadav]

As I said, the easiest way out for you would be to evaluate the resul
of the array formula. As I don't know what exactly is happening, I ca
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)),"")


Manges

 

[malik641]

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