Solved

Excel Lookup Assistance

Posted on 2013-11-14
4
217 Views
Last Modified: 2013-11-15
I have an excel spreadsheet with multiple tabs.  I am working on a tab called summary and I want column C to equal the sum of Tab all deals column M when column J = Civilian
0
Comment
Question by:Matt Pinkston
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39649321
Create a list of all the tabnames in your workbook somewhere, select that list and name it by typing a name in the NameBox just to the left, for example SheetNames

Then enter a formula like:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!J:J"),"Civilian",INDIRECT("'"&SheetNames&"'!M:M")))
0
 

Author Comment

by:Matt Pinkston
ID: 39649330
all I am looking for is one formula for one column

sum of all deals column M when all deals column J = civilian
0
 

Author Comment

by:Matt Pinkston
ID: 39649360
isn't it something like this

=SUMIF(!all dealsJ:J, "Civilian", !all deals M:M)
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39649378
I thought you were going across multiple sheets.... I didn't pick up that it was one sheet...

if it's one sheet named all deals, then

=SUMIF('all deals'!J:J, "Civilian",'all deals'!M:M)
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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.

734 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