Sumproduct using dates
 

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

Sumproduct using dates
 

With the date of interest input in C1,

Try in say, T5:
=IF(C1="","",SUMPRODUCT(--(INT(F5:F150)=C1),R5:R150))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Scopar" wrote:
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

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

Sumproduct using dates
 

Perfect - thanks Max.

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


Thanks again,
Scott

"Max" wrote:

With the date of interest input in C1,

Try in say, T5:
=IF(C1="","",SUMPRODUCT(--(INT(F5:F150)=C1),R5:R150))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Scopar" wrote:
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

Sumproduct using dates
 

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" wrote:

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

Sumproduct using dates
 

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)

"Scopar" wrote in message
...
Perfect - thanks Max.

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


Thanks again,
Scott

"Max" wrote:

With the date of interest input in C1,

Try in say, T5:
=IF(C1="","",SUMPRODUCT(--(INT(F5:F150)=C1),R5:R150))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Scopar" wrote:
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

Sumproduct using dates
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Phillips" wrote:
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)

"Scopar" wrote in message
...
Perfect - thanks Max.

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


Thanks again,
Scott

Sumproduct using dates
 

"Max" wrote in message
...

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


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

Sumproduct using dates
 

"Bob Phillips" wrote:
... gently coerces the TRUE/FALSE ...
I didn't realise you could get poetical about Excel <vbg.

Ahh, must have been the passion in me then <bg
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---