Trying to only SUM values in one column based on if the adjacent cells in another column contain a f-CodePudding

So I'm creating a Google Sheets doc that contains my hours and overtime for an hourly position I have, and how much overtime I'll need to make the amount of money I need to make to survive.

In addition to this I am also trying to track my hours so I can track the actual amount of money I'm making.

So I have a projected clock out time in Column B that uses a formula to tell me what time I need to ideally clock out at based on how many hours I need and what time I clocked in at. When I clock out, I enter the actual time I clocked out at.

In Column C I have the total amount of hours I worked that day, formatted in duration based on the difference between the value in Column A and Column B. If I haven't entered in a value in Column B, it shows me the ideal amount of hours I need that day.

I want to calculate my actual hours worked per week as I'm working that week, so I need to ONLY sum the values in Column C if the adjacent value in Column B is NOT a formula. So I need it to sum the values in column C if I've entered the actual time that I clocked out at in column B.

I've looked up this question and tried multiple solutions I've found here, even tried troubleshooting with ChatGPT, but most are just trying to sum the range that contains the values/formula, and not summing a different column based on if another column has formulas or not.

There seems to be a lot of posts that come super close but don't seem to work for how I need this to work.

Edit: Here is the example sheet.

So F3:F6 are values that have been manually entered, while F:7 has been calculated by a formula.

I need H9 to sum the values of H3:H7, but only the values adjacent to the times in the F column that have been manually entered. In this example, I need it to sum H3:H6 and ignore H7 until I enter a time and remove the formula in F7.

CodePudding user response：

You may filter and check if there is a formula with FORMULATEXT. If there isn't it will throw a #N/A error. Then with ISNA it will keep the values in which its adjacents did'nt have a formula:

=SUM(FILTER(C:C,BYROW(B:B,LAMBDA(b,ISNA(FORMULATEXT(o))))))

CodePudding user response：

try:

=SUMPRODUCT(MAP(F3:F7,LAMBDA(z,IFNA(FORMULATEXT(z),1))),H3:H7)