Hi guys, probably very easy for someone experienced... not managing it at all here.
A file and screenshot are attached below. Can I ask to suggest please?
Thanking in advance!
https://www.excelforum.com/attachmen...1&d=1521042168 [EXCEL]
2018-03-14_1639.png
Hi vill,
This problem is easier if you put all your data into a single table. See the attached. Does this do what you need?
Combine 4 sheets into one.xlsx
If the above doesn't work for you then read:
https://www.laptopmag.com/articles/e...from-multiples
If the above doesn't work then I'm sure Power Query can do your problem. You would need to name your tables in your workbook to do the problem using PQ.
Last edited by MarvinP; 03-14-2018 at 12:29 PM.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thanks for looking into it MarvinP! Much appreciated.
I cannot use pivots here, sorry...
Was exploring the ideas, came up with one way, but its not complete.
Basically, it is perfectly okay to collect all data on TOTALS tab, as I have done in updated file, below row 20 (attached)
I figured some formulas to return most repeated categories, but not able to come up with anything to return the total cost for those top 10. Is there anything we could do with existing example?
I'm thinking if the 'most repeated' (TASK 1) can be sorted, guessing 'most expensive' (TASK 2) could be done in some similar way.
Once again, thanks for looking to help!
https://www.excelforum.com/attachmen...1&d=1521051590 [NEW FILE UPDATED]
in D24
=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!B3:B100"),C24))
"Sheets" is a named range of your sheet names (Sheet1,sheet2 ...)
in E24
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!B3:B100"),$C24,INDIRECT("'"&Sheets&"'!C3:C100")))
in C7
=IFERROR(INDEX($C$24:$C$44,MATCH(1,INDEX(($D$24:$D$44=LARGE($D$24:$D$44,ROWS(C$6:C6)))*(COUNTIF(C$6:C6,$C$24:$C$44)=0),),0)),"")
in D7
=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!B3:B100"),C7,INDIRECT("'"&Sheets&"'!C3:C100")))
in H7
=IFERROR(INDEX($C$24:$C$44,MATCH(1,INDEX(($E$24:$E$44=LARGE($E$24:$ED$44,ROWS(H$6:H6)))*(COUNTIF(H$6:H6,$C$24:$C$44)=0),),0)),"")
in I7
=VLOOKUP(H7,$C$24:$E$44,3,0)
Last edited by JohnTopley; 03-15-2018 at 03:23 AM.
Thank you JohnTopley!! This works wonders :-) Appreciate your help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks