CODEWITHSUNDEEP
google-sheets
William
William

Reputation: 13

sumif with variables in hour format

Well I'm using sumif in google sheet and I'm facing this issue. For my case I want to sum all the numbers according with specific parameters, so SUMIF should answer. But the numbers in the table that should be counted are in hours.

As example =SUMIF($B:$B,9,F:F) where 9 the month, summing all the numbers in the column F. But in F the numbers are displayed as 4:00:00 :

|  B  |     F     |
| --- | --------- |
|  9  |   4:00:00 |
|  9  |   4:00:00 |
|  9  |   4:00:00 |

using the sumif it was supposed to return 12, the number of hours spend working, but instead returns 0.5. If I put '4' instead of '4:00:00' I can solve the problem. But How I can do the math using the hour format?

Upvotes: 1

Views: 2508

Answers (2)

The-Bat
The-Bat

Reputation: 31

I was able to find an alternate solution. You can select the output range. And click on Format -> Number -> Duration. It will show in hour format.

Upvotes: 0

Wicket
Wicket

Reputation: 38200

Short answer

=ArrayFormula(SUM(FILTER(HOUR(F:F),B:B=9)))

Explanation

Google Sheets use serialized numbers for dates and time, using a day as the unit. The result of the OP formula is 0.5 = 1/2 day = 12 hours.

To have as result 12, first we could use HOUR function but SUMIF requires a range as argument, not arrays.

An alternative solution is to change the format of the cell having the formula use the same format as F:F, so a value of 0.5 will be displayed as 12:00:00.

Upvotes: 1

Related Questions