SUM amount worksheets while....

[Kosherboychief]

Hello!
I need to sum a specific cell that is in all the worksheets in a book I am making (ie lets say I have week 1, week 2, week 3 and need to add whatever is in K6 in each one of those sheets). I know how to do this. However, I cannot figure out how to do this while ignoring blanks! I need to ignore blanks because it counts the blanks and I get a zero as a return which messes up an average that uses that SUM.

Please help before my brain explodes and I start speaking in excel codes.

The formula I am using atm is
=SUM('WEEK 1'!M9,'WEEK 2'!M9,'WEEK 3'!M9,'WEEK 4'!M9,'WEEK 5'!M9) and it returns as 0

Whoever finds a fix first is a =HERO(A1:A∞, "MY")

[zbor]

maybe this?

=SUM(WEEK1:WEEK5!M9)

[arthurbr]

The fact that you have blank cells in a sum would not influence your result, nor give 0, as long as they are not all empty
The blank cells would only mess up the average if counted in the the average's divider
To sum contents of the same cell in consecutive sheets, better use a 3D formula

[Kosherboychief]

Alright, @zbor, your solution didn't work at all....
@arthurbr, your suggestion slightly works, but still returns 0, and no, none of the cells have anything in them that is in the formula =SUM('WEEK 1:WEEK 5'!M11), I'm doing a test run to make sure it won't count blanks so all the cells in the range are blank, so the answer should be blank if I am not mistaken...

[arthurbr]

Sum of blank cells always give 0

Or use =if(sum...=0,"",sum...)

[Kosherboychief]

=HERO(COUNTIF(A1:A3245123456234613461, arthurb)))
Thanks so much!

[zbor]

Sheets you summing must be in between of first and last sheet.


Note: week4 is not taken in the SUM. You must move week4 sheet on the left.

[zbor]

For blank you could use something like this:

=IF(COUNT(Week1:Week5!M9)>0, SUM(Week1:Week5!M9), "")

This will take blank if all are blank, SUM otherwise.