asked on

Another Excel ? Multi Worksheets

I now have three worksheets

Cover - the glossy view of sums
data - raw data of job entries
monthly - monthly budget for jobs
xhour - hours spent on each job

the question is I would like to fill a cell on Cover with the sum of all hours in xhour but the kicker is need to do it by segment. There is a one to one corelation between job ids in xhour and monthly but on monthly there is a column called segment.

So for segment = walmart match all soebel IDs from Monthly to xhours and sum hours.

jppinto

You could use SUMPRODUCT() function to sum based on conditions. Have a look at my article that explains the principles of this function:

http://excel-user.blogspot.com/2009/10/sumproduct-sum-values-based-on.html

Or if you have Excel 2010 you could try SUMIFS():

http://excel-user.blogspot.com/2011/02/sumif-and-sumifs-functions.html

jppinto

jppinto

If you need aditional help, you could post a sample sheet for us to take a look.

jppinto

Matt Pinkston

ASKER

here is a sample file test.xlsx

barry houdini

Hello pinkstonmp, you want a formula for B3? What should the result be for your sample, I can't work out if you are taking numbers from monthly or xhours or both....

regards, barry

barry houdini

as a starting point this formula will sum the values in xhours in the rows where the job matches the "walmart jobs" in the data sheet

=SUM(IF(ISNUMBER(MATCH(xhours!A2:A5,IF(data!C2:C5="walmart",data!A2:A5),0)),xhours!B2:B5))

it's an "array formula" that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula, see attached

regards, barry
26845779.xlsx

Matt Pinkston

ASKER

tried this and it did not work:
=SUM(IF(ISNUMBER(MATCH(hours!A2:A25000,IF(data!H2:H5000="defense",data!B2:B5000),0)),hours!I2:I25000))

Updated xls to match testx.xlsx testx.xlsx

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

barry houdini

Typo alert:

"arry formula" should, of course, be "array formula"

barry