I give up, trying to sum with non numeric cells.

[HammerJoe]

I give up... a simples sum is turning out to be too much for me...

I have six sheets named by the weekdays (monday to saturday)

On cel E1 I have the day and month jun-16
On cell C68 I have a formula that either returns a number or
"" (empty)

I am trying to sum the C68 cells of the six sheets only if the month
of each sheet matches current month
(month(monday!E1)=6 for ie.

If one of the C68 cell is empty ("") then I get a #value error and I
cant seem to convert it to zero.


I know it is simple but I cant find the solution.
Please help...

 

[T. Valko]

The easiest way to do this would be to use another cell on each sheet (the
same cell) that tests the month number.

=IF(MONTH(NOW())=MONTH(E1),C68,"")

You can group the sheets together and enter the formula on one sheet and it
will be entered on all the grouped sheets automatically. Just remember to
ungroup the sheets when your done!

Then use a sum formula for all of those cells. Assume you enter the above
formula on each sheet in cell A1.

=SUM(Monday:Saturday!A1)

 

[Sam Wilson]

In each cell C68, return 0 rather than "" - you can then go to format
cells/number and choose "custom", and enter this: #;-#;; as the number
format.


Sam