Solved: SumProduct vs. SUM ??

[debauch]

This is killing Me. I've spent 20 hours scratching me head. I need to derive a sumproduct using a sum? If that even makes sense. I've attached a sample workbook , can someone please tell me im not crazy!!!

[Norie]

What is it you are actually trying to count/sum/average?

[debauch]

it should be weighting the amount of people required "D" against # of calls "B". Trobule is, this is by interval, and I'm trying to get a daily total. So when I sum all the intervals and people required, im then not able to get the weighted avg . the weighted average per interval is simple, but to summarize the daily is more idfficult.

[Norie]

Sorry but you've lost me.:)

You have a formula in F4 that you appear to be saying returns the result you want.

So why do you need another formula if that works?:bug:

[mdmackillop]

You need to be muh more specific in your example. Can you repost it with added comments/data headings. If column A will change (as it appears from your earlier post), then how does this affect your results?

[Bob Phillips]

Is this what you want

=SUMPRODUCT(--(A1:A48="site 1"),B1:B48,D1:D48)/SUM(B1:B48)

[debauch]

I hope this helps.

Basically, if you have say 10 people staffed during 24 intervals, the required people for the day is not 240, but much less. my report keeps summing the totals of the interval, and it can't sumproduct, since I cannot give a range of cells to look at. this is because the data is dynamic.

Let me know if the sample helps at all.

[debauch]

XLD, that is what I want, but to be able to do that by dynamically picking up the correct range. I am not able to specify specific rows.

[Bob Phillips]

=SUMPRODUCT(--(OFFSET($A$1,0,0,COUNTA($A:$A))="site 1"),OFFSET($A$1,0,1,COUNTA($A:$A),OFFSET($A$1,0,3,COUNTA($A:$A))/SUM(OFFSET($A$1,0,1,COUNTA($A:$A))

[debauch]

XLD, that formula is intriguing. However, I'm not sure I know how to apply that. Can you post a sample workbook based on or similar to my attachment? I think I can work with this if I can mirror an example. I've never used 'offset' before.

[debauch]

(duplicate entry by accident)

:Edited:

[debauch]

is there such thing as a sumproductif?

[Bob Phillips]

The SUMPRODUCT formula that I gave you is intrinsically an SPIF because it tests conditions, which is the IF part, and SUMs the resultant PRODUCT of the arrays.

[debauch]

any chance u can post a sample? That formula is pretty complex.

[Bob Phillips]

Take a look at this little walkthrough demo http://www.xldynamic.com/tutorials/VBAExpress/debauch.htm

[mdmackillop]

:clap::clap::clap:

[debauch]

Ok - this is awsome...you couldn't have spelled it out any easier.

Thank You. :friends:

I may not be able to complete my project until Tuesday (long weekend here in Canada). Is it possible to leave that page up for a bit???

[Bob Phillips]

I will leave it up for at least next week.

[Bob Phillips]

So is Canada celebrating Columbus day as well?

[debauch]

It's our Thanksgiving :)

[lucas]

That is a great tool Bob. I have dialup and it worked great. Thanks for sharing and hope we will see more of same.

[debauch]

who-hoo! It worked! When I copied your formula into excel, it must have been adding extra ) or , . So I pulled the formula together peice by peice and it worked! You are a genious XLD. Thanks for your pateince.

These offset formula's are new to me, and will prove useful.

Solved.

[debauch]

I spoke too soon.

Ok, I've attached a sample again. Since there are several 'sites' that may change at any given time, I tried to get the formula to auto sumproduct based on what is in column B. However, as more sites are added, I get a lower result, as it continues to count calls and time that do not match the site count based on the criteria.

It works great, but only for one site. I'm not able to hardcode them all in the "if" part of the formula. Is there a way to only count the calls & time if they meet the criteria in column B?????