Unanswered: Doing a for each for a whole bunch of cells
I have an Excel app which acts as a timesheet. I want to be able to loop thru several cells using a for each statement and see if their are any hours greater than 8 for a particular day and if so, accumulate hours in an OT entitlement field at the bottom of the sheet. Are there any ideas on how to do this?
You can do it with a formula or Visual Basic.
See this post about doing an Array or Index formula to add values based on criteria: Vlookup on more than one column?
To start with I subtracted Start time from End Time and multiplied
by 24 to get a number format from the the Start and End values formated
If C3 is the End time and B3 the Start, this is the formula.
Array Formula to Sum Column 'D':
(Use Ctl+Shft+Enter to apply a formula as an Array Formula
that will add the Curly Brackets and apply the array)
A subtotal formula will total values in a filtered set of records.
Use a subtotal with a custom filter to show values greater than 8.