The Straight Dope

Go Back   Straight Dope Message Board > Main > General Questions

Reply
 
Thread Tools Display Modes
  #1  
Old 05-06-2012, 07:36 AM
tamhav tamhav is offline
Guest
 
Join Date: May 2012
Excel how to add a multiple "name" cell with is calculation to a cell on a separate page

Ok hi guys,

Ive only opened my eyes to the potential of excel recently, but now im stuck on a formula (please note i dont know correct terms to use - sorry if it gets confusing like that)

ok i have a drop down boxs, lets call the names in them product 1, product 2, product 3 etc (in cells E18 - E28)... if u chose a product it automatically spits out a formula in cell G (so G18-G28 all the same formula)

now i need on a seperate sheet, to calculate how many times "product 1" (E cells) + its formula (in G cells) total too.

sheez im sure i make it sound allot more complicated that it is but i need help

thanks guys/girls
Reply With Quote
Advertisements  
  #2  
Old 05-06-2012, 07:51 AM
Zakalwe Zakalwe is offline
Charter Member
 
Join Date: Jul 2000
Location: Tallahassee, FL
Posts: 4,115
It might be better if you put a little more detail in, I'm not sure I completely follow you, but if I understand you correctly, the formulas you are looking for are:

COUNTIF(range, criteria)
and
SUMIF(checkrange,criteria,sumrange)

So, on your other tab:

COUNTIF('Sheet1'!E18:E28,"Product1")
repeat as necessary for Product2-ProductN

SUMIF('Sheet1'!E18:E28,"Product1",'Sheet1'!G18:G28)
again repeat as necessary.
Reply With Quote
  #3  
Old 05-06-2012, 08:05 AM
tamhav tamhav is offline
Guest
 
Join Date: May 2012
Ok i will try explain

On sheet one i have in Row B ,codes in drop down boxes - this pushes out name etc into row C + D and their salaries automatically will go into Row F, in row G is there overtime hours worked.

In row E i have a drop down box as to where the staff worked. (eg SOUTH AFRICA)

Now what i would like on sheet two is to make a analysis for say SOUTH AFRICA - a calculation of everytime it was used in the drop down box + the amount of overtime on that row it calculated - all added up.

This is so hard to put in words sorry
but thanks for the quick reply
Reply With Quote
  #4  
Old 05-06-2012, 08:16 AM
tamhav tamhav is offline
Guest
 
Join Date: May 2012
thanks zakalwe

the last sum worked!!!
clearly i can make anything easy look amazingly difficult!
Reply With Quote
  #5  
Old 05-06-2012, 08:37 AM
Zakalwe Zakalwe is offline
Charter Member
 
Join Date: Jul 2000
Location: Tallahassee, FL
Posts: 4,115
Glad to help!
Reply With Quote
  #6  
Old 05-06-2012, 12:57 PM
K364 K364 is offline
Member
 
Join Date: Nov 2001
Location: Edmonton, Alberta
Posts: 1,806
Pivot tables are very good for this type of problem.
Reply With Quote
Reply



Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


All times are GMT -5. The time now is 07:04 PM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.

Send questions for Cecil Adams to: cecil@chicagoreader.com

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: sdsubscriptions@chicagoreader.com.

Copyright 2013 Sun-Times Media, LLC.