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

**muadebe** · 03-04-2022, 10:28 AM

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.

Thank you for your time.

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

https://www.mrexcel.com/board/thread.../#post-5846385

**TMS** · 03-04-2022, 11:37 AM

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.

**Pete_UK** · 03-04-2022, 11:37 AM

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

**muadebe** · 03-04-2022, 12:23 PM

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.

**Glenn Kennedy** · 03-04-2022, 01:58 PM

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!!

**jeffreybrown** · 03-04-2022, 02:25 PM

Administrative Note:

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.

Please see Forum Rule #3 about cross-posting and adjust accordingly.

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.)

**muadebe** · 03-04-2022, 02:55 PM

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.

**muadebe** · 03-04-2022, 02:57 PM

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

**Glenn Kennedy** · 03-04-2022, 03:22 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Best way to assign a "Bucket" to a "file number" based on data from row

[SOLVED] if formula needed to populate either "1" or "0" based on number of units in another cell

NEED HELP! Code that copies row of data to another sheet based on text "Complete"/"Delete"

[SOLVED] Splitting a Cell based on "/" and adding new Rows based on Part Number Logic

[SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?

Change "Parent" cell based on results of multiple "Children" cells

[SOLVED] Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A"

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions