Sumproduct?

  • Thread starter Thread starter ro
  • Start date Start date
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
 
=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
 
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)
 
=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
 
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
 
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)
 
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
 
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)
 
My Danish version used semicolon but you might have an English version with
Danish
regional settings


--


Regards,


Peo Sjoblom
 
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
 
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
 
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)
 
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
 
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
 
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
 
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...
 
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
 
What is the whole formula now?

--
---
HTH

Bob

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