Hi All…
As per the attached file, the “Aquarius Data” sheet contains the Guests Comments about their experience in this specific restaurant for a specific meal, my target is to summarize all the results or each restaurant and each meal (the example I am listing is for 1 restaurant only which is Aquarius)…. I am showing also the results I need in the “Aquarius Summary” sheet.
Thank you.
In you summary sheet, that would hopefully be organized like the individual sheets, you would need simple sum formulas.
e.g. =SUM('Aquarius Summary:Polaris Summary'!B3)
this will sum cell B3 in all sheets between and inclusive of the first sheet, Aquarius Summary and the last sheet, Polaris Summary...
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thank you NBVC but I honestly didnt understand your approach... where this can be useful for my issue ?? can you put an example on my file...
This is my interpretation of your requirement.
I assume you have a data sheet for each restaurant and then a summary for each restaurant.
I add a summary sheet for all restaurants at the end, with the same layout as the individual restaurant summary sheets
I add simple formula, in B3 of Total Summary:
=SUM('Aquarius Summary:Polaris Summary'!B3)
copied down and across. And similar in other tables below.
Maybe I misinterpreted?
Hi NBVC, I think there is a misunderstanding, the Aquarius Summary sheet is filled manually by me just to show the results I need; getting the total summary will be fine using your approach, but how can I get the summary for each restaurant using formula.. here is the trick I need to find out...
Ok, I would add a helper column to column I, in the Aquarius Data sheet.
Formula in I2:
=INDEX(B3:B$63,MATCH("Restaurant",A3:A$63,0))&CHAR(10)&INDEX(B3:B$63,MATCH("Meal",A3:A$63,0))
Then in B3 of the Aquarius Summary:
=COUNTIFS('Aquarius Data'!$I:$I,$A$1,'Aquarius Data'!$C:$C,$A3,'Aquarius Data'!D:D,"Y")
copied down and across first table.
in other tables, change the $A$1 to top left corner cell of the table.
copied down
You can format the result cells to hide 0's, using Custom and entering Type: 0;-0;;@
That was brilliant... but I modified the file to contain one restaurant in a sheet and I compared the manual results with the formulae ones... I highlighted the discrepancies in yellow... I would be grateful if your could have a final look on he attached file and do the final adjustment…
Another 2 questions: why did you start from I2 which is looking from B3 (next cell)? This causing to have N/A in the last cells… and causing to have wrong data (like in I10 & I11 & I12)
What do you mean by Char(10)? What is 10?
thanks again for all your efforts.
you're right, let's try instead in I3:
=INDEX(B2:B$51,MATCH("Restaurant",A2:A$51,0))&CHAR(10)&INDEX(B2:B$51,MATCH("Meal",A2:A$51,0))
copied down.
Note also, as I mentioned previously, in B13 of the summary you will need to change the $A$1 to $A$13
e.g.
copied down and across.
=IF(COUNTIFS('Aquarius Data'!$I:$I,$A$13,'Aquarius Data'!$C:$C,$A15,'Aquarius Data'!D:D,"Y")=0,"",COUNTIFS('Aquarius Data'!$I:$I,$A$13,'Aquarius Data'!$C:$C,$A15,'Aquarius Data'!D:D,"Y"))
The Char(10) is inserting the non-printable carriage return inputted in A1 of the Summary (it was inserted using ALT+ENTER.. which is Char(10))
Yes that worked perfectly... but I51 gave N/A which I beleive it is fine and won't have any effect ??
Yes, that won't affect it... but if you want it cleaner, try in I3:
=IF(C3<>"",INDEX(B2:B$51,MATCH("Restaurant",A2:A$51,0))&CHAR(10)&INDEX(B2:B$51,MATCH("Meal",A2:A$51,0)),"")
copied down.
Amazing.... I am speechless... I appreciate all your help... Thaaaaaaaaaaaaaaaaanks NBVC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
bishoonline 
Register To Reply
Bookmarks