On the Time sheet, column A has a date in mm/dd/yy format and column D has a duration time. From an overview sheet I'm trying to fill E2 with the total time from all days in April using the following formula:
=SUMIF(Arrayformula(MONTH(Time!A2:A)),4,Time!D:D)
tried researching the answer myself which is where I learned about arrayformula which I never never used before. Don't get why above isn't working.
Here is the spreadsheet (Currently only has one item as it was just started, will eventually have much more data, however the formula should work regardless of number of data points) https://docs.google.com/spreadsheets/d/1G0QDPzrKFsImrIWzCcmC_96qm7xccPdYBIZhvCgMNG4/edit?usp=sharing
You can use SUMIFS for this:
=ARRAYFORMULA(SUMIFS(Time!D1:D7, MONTH(Time!A1:A7),4,YEAR(Time!A1:A7),2025))
Wherein you may remove the YEAR part.
Sample input:
| Time | Duration | ||
|---|---|---|---|
| 4/10/2025 | 1:00:00 | ||
| 4/11/2025 | 2:00:00 | ||
| 4/12/2025 | 3:00:00 | ||
| 5/11/2025 | 4:00:00 | ||
| 5/12/2025 | 5:00:00 | ||
| 4/1/2024 | 6:00:00 |
Sample output:
6:00:00
since even though the last row (in the sample data) is in April, it is from 2024 and not 2025.
TAKE NOTE: Ensure that the format of the cell is in duration (Format > Number > Duration). Else, you may need to add:
=TEXT(<insert whole formula here>,"hh:mm:ss")
Reference: