Share via

SUMIFS Time Values

Anonymous
2017-11-29T21:00:05+00:00

How would I use SIMIFS (or SUMIF) to add all rows with the same pay code?  The Sum range is comprised of time format (13:30) values.  The Criteria range is composed of numbers (generated from a VLOOKUP formula).  The criteria is 231.

The SUMIFS formula below returns #VALUE!.

The SUMIF formula returns 0.

I am looking for a sum equal to either 1.5 or 1:30.

Pay Code Mon Tue Wed Thurs
20 7:15
10 8:00 7:15 8:00
231 0:45 0:45
=SUMIFS(B2:E4,A2:A4,231)*24 USED Vacation
=SUMIF(A2:A4,231,B2:E4) USED Vacation
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2017-11-29T22:14:58+00:00

Did you enter it as an array formula? It works properly in my spreadsheet using your supplied data.

Was this answer helpful?

1 person found this answer helpful.
0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2017-11-29T21:18:09+00:00

    You can share a OneDrive or similar link

    Was this answer helpful?

    0 comments
  2. Anonymous
    2017-11-29T21:17:42+00:00

    Enter this as an array formula by pressing Ctrl+Shift+Enter after entering it:

    =SUM(IF(A2:A4=231,B2:E4))

    Was this answer helpful?

    0 comments
  3. Anonymous
    2017-11-29T21:15:20+00:00

    Hello Ugo,

    How do I upload a workbook?

    I see the 🔗 (link) symbol for an URL, but I don't see an option to upload a file.  I created the table to hopefully show the relevant information.

    Thank you.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2017-11-29T21:01:20+00:00

    Ciao,

    can you please share an example workbook to let me better understand the issue?

    Thanks

    Ugo

    Was this answer helpful?

    0 comments