• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct Formula Help

W

waytaomoy

New Member
Hi, I need your help. I am trying to write a SUMPRODUCT formula to weigh some data.

I am trying to weigh the "Avg Speed" of the COB queue and the HCF queue.

I know the correct answer is 48.04 but I am having a problem writing a concise formula.

If you are all wondering how I got the correct answer I cut and paste the COB & HCF row to a new worksheet and used this formula =SUMPRODUCT(B6:B7,C6:C7)/SUM(C6:C7)

[pre]
Code: 
Row A     Row B    Row C
Queue   Avg Speed  Calls
COB 	   200	     78
COBRA 	    41	     66
DBL	   111	    170
HC-RUS	   179	     19
HC-AS	    54	     12
HCF	    10	    312
HC-COB	    92	      9
LIENS 	    85	      8
LI	    58	     26
[/pre]
Please assist by helping write a formula that will calculate any row that has COB and HCF as a queue. Thanks
 
I'm getting an answer of 48.00, but I'm not sure how you got something different as I tried copying the two rows elsewhere just like you. Perhaps there are unseen decimals...


=SUMPRODUCT(((A2:A10="COB")+(A2:A10="HCF")),B2:B10,C2:C10)/SUMPRODUCT(((A2:A10="COB")+(A2:A10="HCF")),C2:C10)
 
@Luke M

Hi!

Don't worry about the decimal portion of the 48... I was checking precisely that and I got and exact 48, no decimals at all.

Regards!

PS: please stop looking over my shoulder..
 
Mucho Gracias guys.


That worked out superb.

I have another dilemma.


How would I do the same calculation for 12 worksheets?


Meaning the table above is for January, and I have 11 additional worksheets for eacah month of the calendar year.


How can I apply the same formula to get the YTD (Year to Date) or Annual Averages?


Thanks
 
Hi, waytaomoy!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here (better later than never).


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


About your first question, happy you could solve it. All credit to Luke M.


About your new question, do you mean that the data is in range A2:A10 across a worksheet per month? If so, how do you have all the data consolidated? Are there different entries in row A for every month or you can have "DBL" in more than one month? If so, how do you treat them? Could you please elaborate a bit more?


It'd be very useful if you could upload a sample file with a couple of manually entered examples as required output. Please refer to second green sticky post for uploading guidelines.


Regards!
 
Put all the data on one worksheet. But put another column and just put the month name next to the data in case you want to ever refer to a specific months data. If you do ever need to all you do is add a small section to your sumproduct formula.
 
Good morning Sir57, thank you for the generous introduction.


To answer your question.


The data in Column A will are always the same as they are the department names.


Column B is what I am trying to get an answer for. COlumn B and Column C data will always change each month.


What I need to accomplish is have a YTD (Year to Date) calulation that will give me the Avg Speed for each team on the Summary worksheet,


Column A Column B Column C

January 2012

CALL CENTER CALL Volume AVG TALK TIME (ACD)


COB 1,895 178

COBRA 1,549 161

DISABILITY 3,556 127


Imagine I have a sheet for Feb, Mar, Apr, May, Jun containing different data. And then a Summary sheet that calculates Avg for all 6 months.


Sorry I cannot attach a file as I am having difficultly doing so.


Thanks


Sir57 is there an email address I can send the file to you?


EDIT: deleted your email for privacy, if you wish post it again, but not needed, I just emailed you
 
Hi, waytaomoy!


If it isn't an issue related to firewall policies, it'd be helpful if you could upload a file.


If I didn't understand wrong, you want to consider all the data in all sheets as an unique set, is that it?


Regards!


PS: if you wish post here an email address and I'll get back to you.
 
Hi, waytaomoy!

Just sent an email and edited your address in previous post. It's not needed anymore, but if you wish to keep it posted, please post it again.

Regards!
 
Hi, waytaomoy!

Received and downloaded your file. I'll get back to you later this afternoon (GMT-3).

Regards!
 
Hi, waytaomoy!

Haven't had enough time during Friday, it's been a very hard day. Should be coming back on Monday noon. Sorry for the delay.

Regards!
 
Hi, waytaomoy!


Sorry for the delay, but I think we're done.


In sheet Summary:


a) helper columns to retrieve data from each month sheet, in G7 type this formula and copy down and across thru G7:AD18 (don't be afraid, it's just one and only formula to retrieve groups of 6 columns -one for each month- of each of the 4 columns data -B:E-):


=INDICE(INDIRECTO(DIRECCION(FILA($A$7:$A$18);1;1;1;ELEGIR(COLUMNA()-ENTERO((COLUMNA()-6+5)/6)*6;"Jan";"Feb";"Mar";"Apr";"May";"Jun"))&":"&DIRECCION(FILA($A$7:$A$18)+FILAS($A$7:$A$18)-1;5;1;1));COINCIDIR($A7;$A$7:$A$18;0);ENTERO((COLUMNA()-6+5)/6)+1) -----> in english: =INDEX(INDIRECT(ADDRESS(ROW($A$7:$A$18),1,1,1,CHOOSE(COLUMN()-INT((COLUMN()-6+5)/6)*6,"Jan","Feb","Mar","Apr","May","Jun"))&":"&ADDRESS(ROW($A$7:$A$18)+ROWS($A$7:$A$18)-1,5,1,1)),MATCH($A7,$A$7:$A$18,0),INT((COLUMN()-6+5)/6)+1)


b) formula for column D, cells D7:D18, type in D7 and copy down:

=SUMAPRODUCTO(S7:X7;G7:L7)/B7 -----> in english; =SUMPRODUCT(S7:X7,G7:L7)/B7


c) formula for column E, cells E7:E18, type in E7 and copy down:

=SUMAPRODUCTO(Y7:AD7;M7:R7)/C7 -----> in english; =SUMPRODUCT(Y7:AD7,M7:R7)/C7


I hope that works for you. Just advise if any issue.


Regards!


PS: Link to the updated file:

https://dl.dropbox.com/u/60558749/Sumproduct%20Formula%20Help%20-%20HELPSIR57%20%28for%20waytaomoy%20at%20chandoo.org%29.xls


PS2: Sent by mail too.
 
Sir57


I want to thank you for your help.


I got the file, and applied formula.


It works great.


God Bless you Sir.


Have a great day.
 
Hi, waytaomoy!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top