# Summarizing data based "as if" a cell contained different number

1. ## Summarizing data based "as if" a cell contained different number

Current sheet contains labor rates for 4 different cities (city 1, city 2, city 3, city 4). The city is chosen by a drop down list selection and enters value of 1-4 into cell M3 based on city selected. Labor is calculated based on the city selected and total rate, burden, combined is calculated in the worksheet. Summarizing the data for only the selected city is simple.

I would like to be able to summarize the data for all the cities regardless of the city selected so they can be compared in a table. The selected city will have an extensive summary page, but I would like to be able to summarize the totals of some information from all of the cities for comparison (Rate, Total, combined). Not just the selected city.

Is it possible to collect this data when the calculations are based on a single cells value? Or can you collect date as if the cell contained a different value at the same time?

I've attached a brief example sheet, the actual workbook has extensive information and calculations based on the single cell (M3) value for location. I think if I could solve this simple sheet I would be able to figure out how to do the actual workbook.

(I posted this on the Mrexcel forum but received no replies)

2. ## Re: Summarizing data based "as if" a cell contained different number

Fill in the summary table manually and explain how you calculate the figures.

I think the issue is that no-one knows quite what you are trying to achieve.

3. ## Re: Summarizing data based "as if" a cell contained different number

You could make the first table more resilient by using this formula in H9:

=INDEX(\$F\$24:\$M\$28,MATCH(\$F9,\$E\$24:\$E\$28,0),MATCH(VLOOKUP(\$M\$3,\$Q\$3:\$R\$6,2,0),\$F\$22:\$M\$22,0))

and this one in I9:

=INDEX(\$F\$24:\$M\$28,MATCH(\$F9,\$E\$24:\$E\$28,0),MATCH(VLOOKUP(\$M\$3,\$Q\$3:\$R\$6,2,0),\$F\$22:\$M\$22,0)+1)

(The only difference is the +1 near the end of the formula, shown in red). Then you can copy these down. They will give you the same results as the multiple-IF formulae that you currently have, and will still work if the order of the items in column F were to change. You could also link the names of the cities on row 22 directly to the list in column R (e.g. by using =R3 in F22, and =R4 in H22, and so on), so that if you need to change the city names you only need to do this once in column R.

Although that doesn't directly answer your question, it does show you how you can pick the appropriate rate or burden given the city name, so hopefully that will help you to apply it to the table from row 15 onwards. Shout out if you need any further assistance.

Hope this helps.

Pete

4. ## Re: Summarizing data based "as if" a cell contained different number

I think the issue is that no-one knows quite what you are trying to achieve.
The worksheet is designed to calculate pricing for 4 different locations.
Selecting a location dumps all the correct pricing information into each worksheet cell so the correct price for that specific location is calculated
There's a summary sheet that adds everything up and totals the entire workbook for the specific location selected. (Price, total hours per operation, burden, etc.)
Price is totaled for all the different tabs (each tab is a different part)

We would like to be able to see what the total price would have been for the other 3 locations next to the actual location which was selected and computed on the sheet. So if City 1 was selected and run through the sheet, cities 2, 3, & 4 total price would show up next to the summary of city 1 so we can compare.

The only other way I can think of is manually changing the location after completing data entry into the worksheets, and then hard numbering the comparison price. I was hoping this could be done using a formula.

Hope that helps explain what I'm trying to do. Thanks again for your time.

5. ## Re: Summarizing data based "as if" a cell contained different number

Are you still uisng Excel 2013? Unless you are using O365, many/most of the formulae in thsi sheet are likely to be array formulae.

If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

I removed all of your shading to enable you to see exactly where I have changed things. A different colour = a different formula.

It works on my Excel version... it MIGHT work on yours. We'll see!!

6. ## Re: Summarizing data based "as if" a cell contained different number

Welcome to the forum.

We would love to help you with your query; however, it has been brought to our attention the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

7. ## Re: Summarizing data based "as if" a cell contained different number

Are you still uisng Excel 2013?
Thank you for your response. I think this is exactly what I am trying to do (Excel 0365). I will attempt to apply it to the real project next week. I appreciate all your help very much.

8. ## Re: Summarizing data based "as if" a cell contained different number

My apologies. I only mentioned where I had posted it in the original post. I will edit with the direct link.

9. ## Re: Summarizing data based "as if" a cell contained different number

Before you do ANYTHING else, please update your profile to show O365... Click on "my profile" (top of screen), then "about me" then:

There are currently 1 users browsing this thread. (0 members and 1 guests)