Hi,
I was wondering is it possible to have a time format in a condition for SUMIF or COUNTIF formula?
For example I want to sum column A only if B is earlier then 07:30
Can number read time format like this and I could use => ?
My formula is more complicated and I'm using multiple conditions for SUMifs or COUNTifs.
Cheers
Magic
MacBook Pro 13″, macOS 13.3
You can try something like this:
In cell B1 of Table 2.
=SUMIFS(Table 1::A,Table 1::C,"<="&B1)
In cel C2 of Table 1, filled down:
=TIMEVALUE(B2)
TIMEVALUE give the fraction of a day since the previous midnight. Note that in Numbers your cells include both a date and a time even if have formatted the cells to displaying only the time. Using TIMEVALUE avoids the problem of having different "hidden" dates.
Be sure to include the <= between quotation marks.
SG
You can try something like this:
In cell B1 of Table 2.
=SUMIFS(Table 1::A,Table 1::C,"<="&B1)
In cel C2 of Table 1, filled down:
=TIMEVALUE(B2)
TIMEVALUE give the fraction of a day since the previous midnight. Note that in Numbers your cells include both a date and a time even if have formatted the cells to displaying only the time. Using TIMEVALUE avoids the problem of having different "hidden" dates.
Be sure to include the <= between quotation marks.
SG
Hi thanks for the reply!
That helped solve my problem.
Cheers
M
Me too
time as SUMIFS condition
