exceldatetimeformula

Sumif times (datetime format) are greater than 22:30

I have an excel worksheet with a column with times&Dates in Timedate format (dd/mm/yyyy hh:mm) and another with values. I am looking to do a sumif that sums the values if the time (ignoring the date part) is greater or equal to 22:30.

If the datetime is column a and the values are column B i cannt get a sumif(), sum product() or (int) to work

Solution

  • Use FILTER() function filter base on time then sum values. Use MOD() function to separate time from datetime value.

    =SUM(FILTER(B2:B1000,MOD(A2:A1000,1)>=TIME(22,30,0)))

    enter image description here