Google Sheets – Formula Suddenly Stopped Working

formulasgoogle sheets

I have a Google Sheet which requires time to be submitted for different people. I recently had a SUMIFS on another sheet adding the time together based on today() function. The formula I was using is as follows:

=SUMIFS('Annual Data'!N:N,'Annual Data'!G:G,"name of person",'Annual Data'!B:B,TODAY())
  • N:N – Time
  • G:G – Name
  • B:B – Date

As mentioned this was previously working as expected but not it only returns value '0'. The column for 'Time' is formatted to Number.

As an example if John Smith completed the form twice with 5 minutes each. The column in the other sheet would display 10. No idea why this has stop working but it's driving me insane.

Best Answer

Your formula is syntactically sound therefore the issue is likely with your data. Did you copy and paste and/or change the formatting?

I suspect the problem is something like your times are now formatted as text strings, or your dates are actually dates and times.

Troubleshooting

  1. Identify a single row of data that you believe should be added. In other words, it meets the following 3 criteria:
    1. It has time<>0 in Column N
    2. It has "name"="name of person" in Column G
    3. It has date=TODAY() in Column B.
  2. Use a formula to test your assumptions for that row in isolation from the rest of the data.
  3. For example, assuming you believe Row 5 meets all three criteria, the following formula will determine if your assumption is correct: 
    =IF(ISNUMBER('Annual Data'!N5),
    "✓ Time is a Number", "✘ Time isn't a Number!")&
 IF('Annual Data'!G5="name of person",
    " - ✓ Name Matches", " - ✘ Name doesn't Match!")&
 IF('Annual Data'!B5=TODAY(),
    " - ✓ Date Matches", " - ✘ Date isn't Today!")
    Based on the results of this test you may have an "Aha!" moment or at least know where to focus your efforts.
Related Topic