Solved

Excel - Getting some calculations with two worksheets

Posted on 2011-02-24
8
199 Views
Last Modified: 2012-05-11
I have an excel workbook with two worksheets Data - raw data and Cover which is my cover sheet.

On the cover worksheet I want to have some values inserted by calculation on data in the data sheet.

1. I need a subtotal of the column COST when the column SEGMENT equals a specific value.  That column could have 10 values one being  "coscto" lets say I want cell D7 in cover to equal the sum of COST when SEGMENT = "costco"

2. I would also need D9 in COVER to equal the total number of deals when SEGMENT = "costco"

0
Comment
Question by:Matt Pinkston
  • 4
  • 4
8 Comments
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
You can use SUMIF, i.e. in D7 try this formula

=SUMIF(SEGMENT,"costco",COST)

is the 2nd just a case of counting costco in SEGMENT column? If so use COUNTIF in D9

=COUNTIF(SEGMENT,"costco")

or does "deals" refer to a value in another column?

regards, barry

0
 

Author Comment

by:Matt Pinkston
Comment Utility
how does it know to look in sheet DATA
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
Hello pinkstomp,

I assumed that SEGMENT and COST were named ranges in your other sheet in which case their definitions will include the reference to Data worksheet. If you don't have named ranges then you need to refer to the ranges explicitly so if SEGMENT is Data sheet A2:A100 and COST is B2:B100 in that sheet the formulas would be:

=SUMIF(Data!A2:A100,"costco",Data!B2:B100)

and

=COUNTIF(Data!A2:A100,"costco")

If you want to define named ranges and you don't know how to then post back - which Excel version are you using?

regards, barry

0
 

Author Comment

by:Matt Pinkston
Comment Utility
this =COUNTIF(Data!A2:A100,"costco") works great is the a way to do OR

=COUNTIF(Data!A2:A100,"costco" or "walmart" or "kmart")
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
You can use this version

=SUM(COUNTIF(Data!A2:A100,{"costco","walmart","kmart"}))

regards, barry
0
 

Author Comment

by:Matt Pinkston
Comment Utility
is there an equivalent or process for =SUMIF(Data!A2:A100,"costco",Data!B2:B100)
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
Comment Utility
Yes you can do the same there, replace "costco" with an "array constant" showing the text values to count (or sum) an add a SUM function like this

=SUM(SUMIF(Data!A2:A100,{"costco","walmart","kmart"},Data!B2:B100))

you could even list those on the sheet, e.g. if you had costco in H2, walmart in H3 and kmart in H4 you could use this formula for a combined sum

=SUMPRODUCT(SUMIF(Data!A2:A100,H2:H4,Data!B2:B100))

regards, barry

0
 

Author Closing Comment

by:Matt Pinkston
Comment Utility
AWESOME HELP
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now