Updating several cells with a migrating formula?? Hard to explain...

[snswilliams]

Alright, hello everyone! I will get down to it and try to explain this the best I can.

I have a compounding (multiple) countif formula that is referencing a "Break" table and looking for matches on the "schedule" section and then suming the counts in a table by 30 minute intervals. The issue is, now i need to apply this formula to over 700 cells, but have it updating where it's referencing the criteria from the break table to get the right counts for that interval.

As an example, here is my first one for 12:00am to 12:30am ---=COUNTIF(Sat!E64:E120,Breaks!A2)+COUNTIF(Sat!E64:E120,Breaks!A3)+COUNTIF(Sat!E64:E120,Breaks!A4)+COUNTIF(Sat!E64:E120,Breaks!A5)+COUNTIF(Sat!E64:E120,Breaks!A6)+COUNTIF(Sat!E64:E120,Breaks!A7).

the next formula, for 12:30am to 1:00am will need to read like this ---
=COUNTIF(Sat!E64:E120,Breaks!A8)+COUNTIF(Sat!E64:E120,Breaks!A9)+COUNTIF(Sat!E64:E120,Breaks!A10)+COUNTIF(Sat!E64:E120,Breaks!A11)+COUNTIF(Sat!E64:E120,Breaks!A12)+COUNTIF(Sat!E64:E120,Breaks!A13)


Because this is just for Saturday, and i cant just drag or copy>paste, I would have to update the Break references for each 30 minute internal, for all 7 days manually. That's 336 manual cell entries and 2016 countif formula updates. There has to be a more efficient, and effective way to update these formulas... I hope!

See attachments! Thanks for your help in advance.

[Pete_UK]

You could simplify your formula to this:

=COUNTIFS(Sat!E$64:E$120,">="&Breaks!A2,Sat!E$64:E$120,"<"&Breaks!A8)

or, as you have time intervals already in column B of the New sheet, you could do this:

=COUNTIFS(Sat!E$64:E$120,">="&B4,Sat!E$64:E$120,"<"&B6)

which will make it easier to copy down. I don't know why you are using alternate rows in the New sheet - it would be much easier to copy formulae down if you got rid of those blank rows.

Hope this helps.

Pete

[TheN]

If you are keeping the blanks for whatever reason, try creating a helper column that will allow you to sort out even/odd rows (after unmerging cells). That way, you can just double click your new formulas down.

You can create a very simple helper column with this:

=mod(row(a1),2)

that will return a 1 or 0 depending on if the row is even or odd, allowing you to filter out your even/odd rows accordingly (Ctrl+Shift+L to add a filter).

with Pete's answer + this you should be able to maintain your workbook yourself.

[snswilliams]

Ah, yes! That worked! Thanks! I had the spaces in there because it helped me drag the lunch formula so it would like up correctly. I could have done the same with this one but it would have required me inserting 4 between each which seemed even more ridiculous. I thought to myself 'there has to be a better way', and there was thanks to you! Appreciate it!