2

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.

0

1 Answer 1

1

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.
1
  • 1
    Thank you for this response. It was in fact with my data from the other sheet causing the issue. I had stupidly put something into the formula on the sheet the data was pulling from haha! think I just couldn't see it because I had been staring at it that long :/. Thanks again for the help, much appreciated.
    – user322667
    Feb 21 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.