Multiplying by various paremeters to a single cell

[Lithi]

I am struggling to get a solution to work, any advice would be gratefully received.

In 'Sheet 1' I have:

Name % Share
AB 10
CD 20
EF 30
GH 20 etc. etc.


In 'Sheet 2' I have:

Quarter Name Base sum
1
2
3

I am entering multiple Quarters per Name and Base sums.

i.e. I could have AB appearing 5 times with an entry for quarter 1 and 6 times with an entry for Quarter 2.


In 'Sheet 3' I have:

Quarter Total Base Total % Share
1
2
3

'Sheet 3' is a summary sheet, so all entries from Sheet 2 compact down into single row quarters.


I am using the following:

=SUMIF('Sheet 2'!A:A,'Sheet 3'!C2,'Sheet 2'!C:C)

To give me a total base cost against each quarter. I am copying that formula down to each quarter.

I want to calculate in 'Sheet 3' for each quarter 'Total % Share' which would be the total base for each 'Name', multiplied by their respective '% Share' in 'Sheet 1'.

I know I can do this by adding a column to 'Sheet 2', calculating that via a lookup and then summing that in a similar way to the base sum.

However for various reasons, I don't want that info to appear on sheet 2 and would like to avoid a separate 'data table' if possible. I have tried various array formulas but I am only ending up with a 0 result which is clearly wrong.

Can anyone help and I hope the above is clear.

Thanks!

[Mvaldesi]

Can you post a sample of your data? It'll be easier for someone to help you if they can actually see what you're working with.

[Lithi]

I attach sheet.

Cell C:C in Sheet3 is where I am having trouble.

Thanks

[Mvaldesi]

Ok, I'm a little confused. You said:

"I want to calculate in 'Sheet 3' for each quarter 'Total % Share' which would be the total base for each 'Name', multiplied by their respective '% Share' in 'Sheet 1'."

Can you explain this a little more clearly? You're wanting the total of each "Name's" "% Share" of the "Total Base Cost?" Wouldn't that just be the "Total Base Cost?"

[hdolenec]

Is it acceptable to add another column in Sheet 2? For example, I would use VLOOKUP to calculate share for each row using "=VLOOKUP(B2;Sheet1!A:B;2;0)*C2" (for row 2, you can copy function for other rows). The result is displayed in column D.

Then you can simply use the same SUMIF function "=SUMIF(Sheet2!A:A;A2;Sheet2!D:D)" in Sheet 3 to calculate sum of all shares for each quarter.

[Lithi]

It is the additional columns that I want to avoid. That is the method that I am currently using.

The spread sheet that I am using has 000's of lines of data and by adding a further column and putting a formula into each cell is making the sheet large and cumbersome. I also want to avoid it because I have multiple users using the sheet, some of which are not excel savvy and are inserting lines etc. which at times is breaking it.

[Lithi]

To make it a bit simpler, I attach amended worksheet.

On Sheet2 I have added a column which calculates the % Share.

On Sheet3 I have summed this % Share against quarters.

I am trying to make an array formula that will avoid the need to have the additional column in Sheet 2.

Hope that clarifies and thanks for the help so far

[Lithi]

If anyone can help, it would be gratefully received.

[hdolenec]

Sorry, I don't know if this is possible using a single cell. If it is, it's above my pay grade.

One other thing that comes to mind is using another sheet (sheet 4) that summarizes total share by quarter and name. This sheet can be protected and hidden, so people can't access it. Then you use that summary to calculate summary by quarters on sheet 3.

[Mvaldesi]

To make it a bit simpler, I attach amended worksheet.

On Sheet2 I have added a column which calculates the % Share.

On Sheet3 I have summed this % Share against quarters.

I am trying to make an array formula that will avoid the need to have the additional column in Sheet 2.

If your simplified solution provided the desired result, is it possible for you to "copy/paste special-values" after the calculation has been performed, then simply delete the additional column? Or is it necessary to have the formulas stay in there?

[Lithi]

The actual spread sheet is continually being updated, that is why I am an trying to find a single cell solution, so that the figure updates as data is put into Sheet2.

Thanks for the help and suggestions so far.

[Mvaldesi]

In that case, similar to hdolenec, I'd suggest a combination of hiding and protecting cells and/or sheets.

Perhaps duplicate Sheet2? Have Sheet2 with the column you DON’T want to share (the one that got you to your desired result) be hidden and protected and then have its duplicate [Sheet2(2)] be a cell-to-cell copy just without the column you don’t want shared (i.e.: Sheet2!A1=Sheet2(2)!A1, Sheet2!A2=Sheet2(2)!A2, etc.)

Not the most elegant solution, but it gets you where you’re trying to go…

EDIT: I did some research and found a "VeryHiddenSheet" in VBE that ensures a hidden tab can't be unhidden by any users, it doesn't even show up in 'unhide' dialogue boxes, so user won't even know it's there. Kinda cool; never knew this was a thing!

[Lithi]

Thanks for the help both.

VeryHiddenSheets do sound interesting!

[Mvaldesi]

It's easy; check it out below; just don't tell any of the end users of your report how to do it!

http://www.techrepublic.com/blog/mic...lly-hide-them/

If you think your issue is resolved (as best we could muster,) go ahead and mark the thread SOLVED!

Best wishes!

[Lithi]

I solved my problem and attach the solution.

Thanks for all the help and the handy tips on hidden hidden sheets!