SumProduct using dates

hello

having some major issues with sumproduct + date formula

basically i have in summary worksheet D9 = 30-may-2011 ...this drop down shows first monday of every week.

issue is trying to calculate for month-to-date, for the above categories...im not sure how to tackle this...i tried the formula below, but getting no luck

c)$ of extra charges ="Y" ....MONTH-to-DATE

=SUMPRODUCT(--(MONTH('G.S - Report'!$K$8:$K$65536)=MONTH(D10)),--(YEAR('G.S - Report'!$K$8:$K$65536)=YEAR(D10)*('G.S - Report'!$N$8:$N$65536)))

can someone pls help!

i have highlighted in red the issues in the summary worksheet.

ive been on this for the past 3 hrs so if someone can pls help!

When I apply Autofilter to the GS Report and filter column K for dates between May 30, 2011 and 6 days after (June 5, 2011), and then filter column Q for "Y" only, I count 36.. which is your sumproduct result....

The #VALUE error is because you have text in a row or two in column N...

Instead of multiply the summing range, separate it with a comma...

=SUMPRODUCT(--('G.S - Report'!$K$8:$K$65536>=$D$10),--('G.S - Report'!$K$8:$K$65536<=$D$10+6),--('G.S - Report'!$J$8:$J$65536=K25),('G.S - Report'!$N$8:$N$65536))

Also, you should avoid such large ranges with Sumproduct.

hello

i might of jumped the ship going crazy with the spread...it appers to be right, thx u so much!


however the main issue appears to be how i can calculate for the MONTH-TO-DATE using the value in cell D10

=SUMPRODUCT(--(MONTH('G.S - Report'!$K$8:$K$65536)=MONTH(D10)),--(YEAR('G.S - Report'!$K$8:$K$65536)=YEAR(D10)*('G.S - Report'!$N$8:$N$65536)))

the issue is, May30 is Monday and June is Tuesday, so when i try to sum for the month, it is searching for May only...is there a way to make it search for May and June for instance where the months clash?

also, when i try to calculate

$ of extra charges

=SUMPRODUCT(--('G.S - Report'!$K$8:$K$65536>=$D$10),--('G.S - Report'!$K$8:$K$65536<=$D$10+6),--('G.S - Report'!$Q$8:$Q$65536=K25)*('G.S - Report'!$N$8:$N$65536))

it is giving me a #VALUE error.

i have updated the workbook in the original attachment,...if u can pls advise!

i really appreciate the help!

I mentioned above why you are getting #VALUE error. replace the * before the last array with a comma... and in this one, you need another closing bracket after Year(D10)....

Please Login or Register  to view this content.

this gives 0, which is correct.. but I do not understand :

the issue is, May30 is Monday and June is Tuesday, so when i try to sum for the month, it is searching for May only...is there a way to make it search for May and June for instance where the months clash?

i gotta take off right now, so hopefully you can resolve it yourself or with someone else's continued support... or I will get back to it tonight or tomorrow...

What about the part where I said I don't understand your problem? Are you going to elaborate on that?

If we assume given .xls SUMIFS etc are precluded then in terms of row 19

Please Login or Register  to view this content.

You should be able to determine the remainder from the above.

I would add that you should not be using such vast precedent ranges with SUMPRODUCT and should consider reducing scope to a reasonable size either via smaller hard-wired range or via non-volatile Dynamic Named Ranges.

To get a unique count in this manner a Frequency Array would arguably suit best.

Before continuing however I would reiterate the point regards Dynamic Source Range.

First create a Defined Name as follows:

Please Login or Register  to view this content.

We can then, if we wish, adjust your existing SUMPRODUCTs to utilise this smaller/dynamic range, e.g:

Please Login or Register  to view this content.

From the above you should be able to work out the remainder of the existing SUMPRODUCT calcs
(i.e. modify the >= date for the columns, modify the 8 (Col J at source) in the final index to reflect the appropriate column for rows 19 & 21 etc...)

In terms of your unique count...

Please Login or Register  to view this content.

Note the Array entry requirement. If the Array has been set correctly the above will appear encased within { }
(these can not be added manually)

The formulae in row 29 can be copied to rows 31 & 33, however, yo uwill need to modify your INDEX column 8 (test re: K29) as appropriate (as per the SUMPRODUCT example earlier).
Remember: When you modify an Array you must remember to confirm the alteration with CTRL + SHIFT + ENTER so as to reset the Array successfully.

I'm afraid I can't create your file for you as I am at work but the above should suffice.

I was going to suggest similar with:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER and similar for other cells with appropriate changes.

But I too strongly recommend, as I did initially in this thread also, to not use the whole column in your formula and instead reduce it with a smaller last row number or dynamically as DO showed you.

final question

i am trying to get the "average # of invoices per store"

i tried to re-use the formula you provided but getting a value error.

Average # of invoices per store

=AVERAGE(IF(FREQUENCY(IF(INDEX(_Data,0,10)>=$D$10,IF(INDEX(_Data,0,10)<=$D$10+6,IF(INDEX(_Data,0,11),MATCH(INDEX(_Data,0,6),INDEX(_Data,0,6),0)))),ROW(INDEX(_Data,0,6))-ROW(INDEX(_Data,1,6))+1)>0,1))

confirmed by ctl+shift+enter

pls advise, thx u so much

i have attached a sample with red highlighted cell....thxs u once again

I don't see too many duplicate stores....

This formula below gives me a result of 1 invoice per store...

Please Login or Register  to view this content.

Array entered.

Does this one work?

Please Login or Register  to view this content.

It averages column N based on your date range and column J having "Extra Shred" service.

Seems right. I tried it too and get the same number...

When I manually filter the data for that date range and y in column Q, all the values in column N average to the same number.... and I don't see any duplicate store # or invoice #, so I am not sure... if you are just looking for average on date criteria and column Q is Y, then even this formula would work:

Please Login or Register  to view this content.

confirmed with CSE keys...

it says usa in yours

Please post your attachment with the new formulas.

Can you tell me how you manually checked? Are there duplicate store numbers?

When I filtered for dates from June 6 to June 12 and averaged column O manually, I got 1.427.

I also got that number with this formula:

Please Login or Register  to view this content.

Real quick sorry, is there a chance duplicate entries will be counted in the formula you provided?

but to your point, you just made me realize that store #s can repeat as they might call for additional services, so in that case, the only place where a value will be unique is the invoice #, that should not repeat so *damit*...i might have to change all the reference from column 6 in the index to invoice

so i have a question, if the invoice # is unique, will i need a frequency formula?

In the last formula yes, duplicate stores will be incorporated, in the previous formula where you got 1.06, I am not 100% sure, because I am not 100% sure what your expectation is.

Can you tell me what exactly you expect the result to be? And how you come to that?

im getting confused over this now jeez!

me too.

If the invoices are all unique, then the store shouldn't matter, correct?

If so, then the smaller formula:

Please Login or Register  to view this content.

should do the trick... it averages column O based the date criteria only. I get 1.427 with that formula.

c

yeah, it appears the frequency was not really needed as the store # can appear multiple times as they might call for service a few times over a week, month etc.

so that changes alot of the calculation and makes everything pretty simply.

so for: # service requests per store, i used the following formula, but getting DIV/0 - line 59 on summary

{=AVERAGE(IF(((INDEX(_Data,0,10)>=$D$10)*(INDEX(_Data,0,10)<=$D$10+6)),INDEX(_Data,0,8)=$K59))}

basically, checking the date criteria and asking to average # of items that say "Extra shred" or K59

when i do a manual filter, there are 225 instance where extra shred appears but the formula is returning a div/0....any idea why?
thxs so much...this will prob be my final post with this.

also, for line 61
$ extra service charges per store
how can i add to the formula above another criteria...i.e. search by extra and sum the invoice amount / store?

thx u so much sir!

The conditional parts need to stay together separated by the *, then you need to insert the range you want averaged after the comma, in the "value_if_true" part of the IF() function. And we can get rid of one opening bracket after the IF...

so:

Please Login or Register  to view this content.

here I assumed you wanted to average the 13 column again

Originally Posted by NBVC

The conditional parts need to stay together separated by the *, then you need to insert the range you want averaged after the comma, in the "value_if_true" part of the IF() function. And we can get rid of one opening bracket after the IF...

so:

Please Login or Register  to view this content.

here I assumed you wanted to average the 13 column again

so ur formula is for row 59: # of service request per store (Avg).
...column 13 is days to complete....so
so there were 225 extra shred request, so per store that is....1.41..base on the formula for the week of 6-june-2011?

did i follow that correctly?

should it be....
the formula divided by count of invoices for that week?
so i.e. counta(index(_data,0,6) ....6 being the store?
ur thoughts?
thxs for the help

also, finally

i used this formula for .... # of extra service charge invoices per store (row 63)

=AVERAGE(IF((INDEX(_Data,0,10)>=$D$10)*(INDEX(_Data,0,10)<=$D$10+6)*(INDEX(_Data,0,15)=$K63),INDEX(_Data,0,6)))
and get 606....that seems rather high?

There seems to be 182 extra shred requests, not 225... and the average of the days to complete is 1.41

The formula is already averaging (it is sum/count ... but you still need to define the criteria).

That formula says you are averaging column H which is your Store numbers... I don't think that is what you want.

Are you counting or averaging? And if averaging, which column?

To just count you need to go back to the SUMPRODUCT:

=SUMPRODUCT(--(INDEX(_Data,0,10)>=$D$10),--(INDEX(_Data,0,10)<=$D$10+6),--(INDEX(_Data,0,15)=$K63))

So if you averaging per store, does column O values not apply?

What are the actual (accurate!) expected results?

I meant column O in your Summary sheet. You have the Average(Frequency) formulas there. Are those results correct (i.e. 1.08)?

Originally Posted by NBVC

I meant column O in your Summary sheet. You have the Average(Frequency) formulas there. Are those results correct (i.e. 1.08)?

yes they are correct. can i simply use that for row 59?

the only reason why i didnt go forward with the frequency on those is bc i was told
1) a store can appear a few times, however will alwasy create a unique invoice #.

so can i simply use that formula for in column O row 59? and the other row 65 and 69 ??? kindly let me know, thx u

If you are trying to find average number of invoice per store, then you want to count the number of invoices in each store (ie. store may appear multiple times), then average that number over the number of stores?

I think that is what the formulas in column O of the summary are doing.

I used:

Please Login or Register  to view this content.

CSE confirmed and got 1.. which seems correct after filter the source data.