Sumproduct?

[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

 

[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

 

[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)

 

[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)

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

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

 

[ro]

November

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

last week

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

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

 

[ro]

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

ftp://freecall:[email protected]/excelfiddle/joblog.zip (4k)

 

[ro]

ftp://freecall:[email protected]/excelfiddle/joblog.zip (4k)

My apologies,

ftp.dxp.dk/excelfiddle/joblog.zip
user: freecall
pass: dxpfree

 

[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)

 

[ro]

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

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

 

[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)

 

[Peo Sjoblom]

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


--


Regards,


Peo Sjoblom

 

[ro]

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

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

 

[ro]

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

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

 

[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)

 

[ro]

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

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

 

[ro]

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

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

 

[ro]

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

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

 

[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)

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

 

[ro]

Sorry, I had one too many brackets in there

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

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

Rasmus

 

[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)