Remember Me?

 Straight Dope Message Board Remember Me?

#1
05-06-2012, 08:36 AM
 tamhav Guest Join Date: May 2012 Location: Limpopo, South Africa Posts: 6
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
#2
05-06-2012, 08:51 AM
 Zakalwe Charter Member Join Date: Jul 2000 Location: Tallahassee, FL Posts: 4,758
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)

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

SUMIF('Sheet1'!E18:E28,"Product1",'Sheet1'!G18:G28)
again repeat as necessary.
#3
05-06-2012, 09:05 AM
 tamhav Guest Join Date: May 2012 Location: Limpopo, South Africa Posts: 6
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
#4
05-06-2012, 09:16 AM
 tamhav Guest Join Date: May 2012 Location: Limpopo, South Africa Posts: 6
thanks zakalwe

the last sum worked!!!
clearly i can make anything easy look amazingly difficult!
#5
05-06-2012, 09:37 AM
 Zakalwe Charter Member Join Date: Jul 2000 Location: Tallahassee, FL Posts: 4,758
#6
05-06-2012, 01:57 PM
 K364 Member Join Date: Nov 2001 Location: Edmonton, Alberta Posts: 2,290
Pivot tables are very good for this type of problem.

 Bookmarks

 Thread Tools Display Modes Linear Mode

 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 Rules
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Main     About This Message Board     Comments on Cecil's Columns/Staff Reports     General Questions     Great Debates     Elections     Cafe Society     The Game Room     Thread Games     In My Humble Opinion (IMHO)     Mundane Pointless Stuff I Must Share (MPSIMS)     Marketplace     The BBQ Pit

All times are GMT -5. The time now is 04:36 AM.

 -- Straight Dope v3.7.3 -- Sultantheme's Responsive vB3-blue Contact Us - Straight Dope Homepage - Archive - Top

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