Customizing dynamic chart to include sum of files pr. day?

[ro]

Hi world,

This is my log (20k) <http://img2.freeimagehosting.net/uploads/
32373110dd.png>

I have been trying to adapt a dynamic chart tutorial from Jon Peltier
into a Excel filetransfer log. Dates along the x-axis and the sum of
files transferred per day along the y-axis. Only problem is that I
cant figure out how to plot the sum of total files transferred pr.
date.

In my log I have multiple entries for the day 23-11-2007, but instead
of a total file count for the whole day Excel only use the last entry
(A16).

This is my named ranges:

AllDates:
<=Data!$A$1:INDEX(Data!$A:$A;MATCH(9,99999E+307;Data!$A:$A))>
ChartDates:
<=OFFSET(AllDates;MATCH(StartDate;AllDates;
1)-1;0;MATCH(EndDate;AllDates;1)-MATCH(StartDate;AllDates;1)+1;1)>
FileCount:
<=OFFSET(ChartDates;0;5)

I have had very competent help from this group before constructing a
sumproduct function for the log. Having the chart plot total file
count pr. date is the last hurdle. I have tried inserting a COUNTA
function from another Peltier tutorial but this didn't do anything. I
assume one of my ranges requires a little bit of tweaking. Anyone?

Best regards,
Rasmus

 

[hall.jeff]

I don't believe that you can imbed array ranges into a chart's
range... so you're going to be stuck using some sort of sumif in a
separate column... I don't like that solution because, with your file
size, sumif is not particularly efficient (using the sumproduct array
trick might be quicker... haven't tested)... =dsum is another
choice...

You might be better off writing a macro that ripped through the data
and created a clean grid with the totals that you want and then chart
from that new grid...

 

[ro]

Hi,

Thanks for a quick reply

I don't believe that you can imbed array ranges into a chart's
range... so you're going to be stuck using some sort of sumif in a
separate column... I don't like that solution because, with your file
size, sumif is not particularly efficient (using the sumproduct array
trick might be quicker... haven't tested)... =dsum is another
choice...

Actually I dont need the chart to plot filesize. A chart over total
files pr. day is all Im looking for.

You might be better off writing a macro that ripped through the data
and created a clean grid with the totals that you want and then chart
from that new grid...

Macros is new to me but it sounds interesting. In your opinion will
this require expert knowledge?

Rasmus

 

[hall.jeff]

Something like this would require a working knowledge of VBA... if you
don't feel that that's you, I'd recommend just adding a tab that has a
summary grid that your chart can refer to...

 

[ro]

Something like this would require a working knowledge of VBA... if you
don't feel that that's you, I'd recommend just adding a tab that has a
summary grid that your chart can refer to...

Ok, can you possible give me an example of a function (sumif/
sumproduct/dsum?) adding all the log entries pr. date in another tab?
Just something to get me started. Thanks

Rasmus

 

[ro]

On Nov 29, 4:43 pm, (e-mail address removed) wrote:

Ok, can you possible give me an example of a function (sumif/
sumproduct/dsum?) adding all the log entries pr. date in another tab?
Just something to get me started. Thanks

Doh! - I allready had those sumproducts ;-)

BTW. Pivottable seems to do the job with regards to my dynamic chart
request.

Best regards,
Rasmus