Excel ref error when summing non contiguous cells

[KarlChea]

Hey guys, I'm trying to sum cells that are 12 cells apart for a rota, the only issue is that at the end of everyday the rota is cut down to what was used that day but then my SUM for totals goes to REF. example: I have =SUM(K1+K12+K24+K36) , The value of each k cells is ALWAYS going to be either 1 or blank, I just want to add the 1s together. This works fine, but if we only get to K24 that day, we need to delete K36 to setup for tomorrow, but once I do that I get a ref error. I know I can just remove K24 from the formula but this is for 11 separate sums every day so it would be handy if there was a way to ignore the ref error after deleting a non contiguous cell.

I've tried basically everything I can find but most things I can see help prevent the ref error with contiguous cells and don't work for my needs, any help would be appreciated as you'll make me look good in work lol

 

[offthelip]

Welcome to the forum
Why not select the row and then press the delete key which clears the row instead of removing the row with the delete row function. This avoids the ref problem??

 

[Fluff]

How about

=SUM(INDEX(K:K,SEQUENCE(4,,0,12)))

 

[KarlChea]

How about

=SUM(INDEX(K:K,SEQUENCE(4,,0,12)))

When I'm entering this it is selecting all of the K row but I only want specific K's included that are not sequential, any idea?

 

[Fluff]

Firstly K is a column not a row.
Whilst it may highlight the entire column it is only returning values from the 4 cells you mentioned.
Try it & see

 

[KarlChea]

Firstly K is a column not a row.
Whilst it may highlight the entire column it is only returning values from the 4 cells you mentioned.
Try it & see

Couldn't get it to work on mine, not sure what I'm doing wrong ? I managed it by doing =SUM(IFERROR(K4:K4)IFERROR(K16:K16) etc etc and it was able to add it up and delete cells with no ref error, thanks a lot for ur help

 

[Fluff]

In what way didn't it work?