Sumproduct using dates
Time is irrelevant, that should work but the ranges MUST be the same size
either
=SUMPRODUCT(--(TEXT(F8:F150,"mmmm")="June"),R8:R150)
or
=SUMPRODUCT(--(TEXT(F5:F150,"mmmm")="June"),R5:R150)
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"Scopar" wrote in message
...
Hi,
I'm trying to create a SUMIF formula (although suspect a SUMPRODUCT
formula
may be better) based on a date. I have a spreadsheet that is linked to a
database and it displays individual entries for each work task that is
entered into it throughout the day. I'd like to be able to add up how
much
time is spent on a particular task (such as processing e-mails) at the end
of
the day.
I've seen a formula in another post that I think is very close to what I
need, but I can't get it to work. I'm pretty sure it's because my date
entries also include a time and no two of these are alike.
The formula which I think I need to base my formula on is:
=SUMPRODUCT(--(TEXT(F8:F150,"mmmm")="June"),R5:R150)
Where column F contains the date (in the format "dd/mm/yyyy hh:mm:ss") and
column R contains the time spent on the task (in minutes).
I just need a slight modification so that instead of using "June" I want
to
base it on a date that I enter into another field, say C1.
Just to complicate matters further, the data is in a pivot table. I'm not
sure if that makes a difference.
Thanks in advance for any help you can offer.
Scott
|