Sumproduct using dates

[Guest]

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

 

[Guest]

With the date of interest input in C1,

Try in say, T5:
=IF(C1="","",SUMPRODUCT(--(INT(F5:F150)=C1),R5:R150))

 

[Bob Phillips]

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)

 

[Guest]

Perfect - thanks Max.

Just out of curiosity, what does the "--" in "SUMPRODUCT(--(" actually do?


Thanks again,
Scott

 

[Guest]

Thanks Bob.

Sorry, that was a typo. I didn't realise the ranges had to be the same
size, so it's good to know that, but they were actually the same size in the
formula - just not when I typed it this time around.


Thanks,
Scott

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Scopar, thanks for calling back. Glad it fit what you were after.

The "--" in the part: --(INT(F5:F150)=C1) gently coerces the TRUE/FALSE
returns in the comparison done within the outer parens, ie: INT(F5:F150)=C1,
to 1's/0's. This conversion is required to enable SUMPRODUCT to then
"cross-multiply" the 1's/0's with the corresponding numbers within R5:R150
and total the resulting products.

Do take the time/effort to study Bob's excellent paper on SUMPRODUCT at his
link. You'd definitely benefit a lot from it, as I and countless others did.

---

 

[Bob Phillips]

... gently coerces the TRUE/FALSE ...

I didn't realise you could get poetical about Excel <vbg>.

 

[Guest]

I didn't realise you could get poetical about Excel <vbg>.

Ahh, must have been the passion in me then <bg>

---