Sumproduct?

R

ro

Hello world,

I am working with a xls log function in a filetransfer workflow. The
worksheet is like this:


Date/Time Job name Files in job
01-11-2007 Test01.jpg 1
02-11-2007 Test02.jpg 1
02-11-2007 Test03.jpg 1
03-11-2007 Test04.jpg 1
04-11-2007 Test05.jpg 1

My goal is to process this data in another worksheet. So far I have
succeeded in having Excel adding the numbers in the "Files in job"
column and displaying the sum in the second worksheet - yes, wow! ;-)
Whats next is to have Excel add the amount of files transferred for a
given timespan, display this in the second worksheet along with a
graph illustration. Say I want the number of files transferred
november or maybe the past week - is this possible?
I have been on google for the past hours looking for a tutorial or
something to help me construct this function but until now my
desperate fiddle in Excel with sumproduct has been in vain..

Sincerly yours,
Rasmus Olsen
Denmark
 
P

Peo Sjoblom

=SUMPRODUCT(--(YEAR(A2:A50)=2007),--(MONTH(A2:A50)=11),C2:C50)

or something like this in Danish

=SUMPRODUKT(--(ÅR(A2:A50)=2007);--(MÅNED(A2:A50)=11);C2:C50)


Where A2:A50 holds the dates and C2:C50 the files in job

for the past week use


=SUMPRODUCT(--(A2:A50>=TODAY()-7),C2:C50)

or in Danish

=SUMPRODUKT(--(A2:A50>=IDAG()-7);C2:C50)

the latter requires that your pc clock is up to date and I assume there
cannot be any file transfers in the future


--


Regards/M.v.h.


Peo Sjoblom
 
B

Bob Phillips

November

=SUMPRODUCT(--(MONTH(date_rng)=11),files_rng)

last week

=SUMPRODUCT(--(date_rng>TODAY()-7),--(date_rng<=TODAY()),files_rng)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

ro

=SUMPRODUCT(--(YEAR(A2:A50)=2007),--(MONTH(A2:A50)=11),C2:C50)
Where A2:A50 holds the dates and C2:C50 the files in job
for the past week use
=SUMPRODUCT(--(A2:A50>=TODAY()-7),C2:C50)
Click to expand...

Thank you for your reply Peo. If I copy/paste your form in my workbook
I get the "The formulay you entered contains an error" dialogue. If I
press ok for assistance in entering the form Excel places the cursor
at the comma right after the '=2007)'. If I delete this comma the
error dialogue returns and places the cursor at the next comma. When
the last comma gets deleted Excel reports back "Excel found an error
[...] Do you want to accept the correction proposed below? Press yes
and Excel enters '=SUMPRODUCT(--(YEAR(A2:A50)=2007)--
(MONTH(A2:A50)=11)*C2:C50)' and I end up with the value '21' in the
cell?
I assume there cannot be any file transfers in the future
Click to expand...

The workflow is running 24/7/365 so I need to have Excel "see" the
entire column.

BTW. I have uploaded my workbook to <ftp://freecall:[email protected]/
excelfiddle/joblog.zip> (4K) in case you want to test.

Sincerly yours,
Rasmus
 
R

ro

November

=SUMPRODUCT(--(MONTH(date_rng)=11),files_rng)

last week

=SUMPRODUCT(--(date_rng>TODAY()-7),--(date_rng<=TODAY()),files_rng)
Click to expand...

Mr. Phillips, thank you for your reply. As with Peos form suggestion
it apperars that Excel wont accept the commas. Am I doing something
wrong when entering these forms?

Can you elaborate a little on how to set the ranges?

Sincerly yours,
Rasmus
 
B

Bob Phillips

Try using semi-colons ( ; ) as you may have a continental edition of Excel.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

ro

Try using semi-colons ( ; ) as you may have a continental edition of Excel.
Click to expand...

Yup, that was it. Peos form works fine now but yours returns "#NAME" -
I believe I have to define the range?

Sincerly yours,
Rasmus Olsen
 
B

Bob Phillips

Yes, mine was generic, you need to substitute the actual ranges in there, or
used named ranges.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
P

Peo Sjoblom

My Danish version used semicolon but you might have an English version with
Danish
regional settings


--


Regards,


Peo Sjoblom
 
R

ro

My Danish version used semicolon but you might have an English version with
Danish
regional settings
Click to expand...

Hi Peo, a little help modifying your form to look at the entire A and
C column? I have tried the A:A and C:C but receives #NUM.

Sincerly yours,
Rasmus
 
R

ro

Yes, mine was generic, you need to substitute the actual ranges in there, or
used named ranges.
Click to expand...

Im a little thick - please bare with me ;-) Is it possible to have the
form look at entire columns and not a defined range? Of course I can
use A2:A1000 but there must be some kind of command for this..

Yours sincerly,
Rasmus Olsen
 
B

Bob Phillips

No you cannot. SP is a formula working on arrays, and arrays cannot include
whole columns in pre-2007 Excel.

You could use dynamic ranges

OFFSET(A2,0,0,COUNTA($A:$A)-1),1)

instead of A2:A1000, but you have to be careful that you base all ranges on
the same count. For instance if you used A2:A1000 and B2:B100 you would use

OFFSET(A2,0,0,COUNTA($A:$A)-1),1)

and

OFFSET(B2,0,0,COUNTA($A:$A)-1),1)

note the count is based upon the same column.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

ro

OFFSET(A2,0,0,COUNTA($A:$A)-1),1)
OFFSET(B2,0,0,COUNTA($A:$A)-1),1)
Click to expand...

Excel reports back an error. If I press ok it highlights the COUNTA
part. A reference is displayed: =FORM(reference; rows; cols; [height];
[width]) - but even though I replace commas with semis and insert
brackets I get the error message. A little help mr. Phillips ;-)

Rasmus
 
R

ro

OFFSET(A2,0,0,COUNTA($A:$A)-1),1)
OFFSET(B2,0,0,COUNTA($A:$A)-1),1)
Click to expand...

Excel reports back an error. If I press ok it highlights the COUNTA
part. A reference is displayed: =FORM(reference; rows; cols; [height];
[width]) - but even though I replace commas with semis and insert
brackets I get the error message. A little help mr. Phillips ;-)

Rasmus
 
R

ro

OFFSET(A2,0,0,COUNTA($A:$A)-1),1)
OFFSET(B2,0,0,COUNTA($A:$A)-1),1)
Click to expand...

Excel reports back an error. If I press ok it highlights the COUNTA
part. A reference is displayed: =FORM(reference; rows; cols; [height];
[width]) - but even though I replace commas with semis and insert
brackets I get the error message. A little help mr. Phillips ;-)

Rasmus
 
B

Bob Phillips

Sorry, I had one too many brackets in there

OFFSET(A2,0,0,COUNTA($A:$A)-1,1)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



OFFSET(A2,0,0,COUNTA($A:$A)-1),1)
OFFSET(B2,0,0,COUNTA($A:$A)-1),1)
Click to expand...

Excel reports back an error. If I press ok it highlights the COUNTA
part. A reference is displayed: =FORM(reference; rows; cols; [height];
[width]) - but even though I replace commas with semis and insert
brackets I get the error message. A little help mr. Phillips ;-)

Rasmus
Click to expand...
 
R

ro

Sorry, I had one too many brackets in there

OFFSET(A2,0,0,COUNTA($A:$A)-1,1)
Click to expand...

Hi Bob, it works now but returns a high value (39387) - could Excel be
adding the dates to the calculation?

Rasmus
 
B

Bob Phillips

What is the whole formula now?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Customizing dynamic chart to include sum of files pr. day? 5
New to SUMPRODUCT .. is there a better way? 1
problems when saving as comma delimited text file 1
slow calculation of rank and percentile in Excel 2007 3
tt:mm:ss interferes with defined range? 14
COUNTIF? SUMIF? SUMPRODUCT? IF? 2
Copy cells from one worksheet and paste to incrementing sheets? 2
Slow Excel 2007 calculation in a workbook and one solution 4

Top