I have a below table:
table.jpg
And need report like below in another sheet:
report.jpg
Please help!
Regards,
I have a below table:
table.jpg
And need report like below in another sheet:
report.jpg
Please help!
Regards,
Last edited by vishiabj; 09-19-2019 at 09:20 AM.
Use a Pivot Table.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks TMS, but I need without pivot table as I want to add more columns to report and to link with online app.
Regards,
If you only provide part of your requirement, I’m afraid you're not going to get a solution that meets all your needs.
I would probably use an Advanced Filter to extract the relevant unique entries and the SUMIFS to get the totals. You can't use VLOOKUP for what you want.
Pivot is your best option along with sumifs or use calculated fields from the pivot
Too bad you dont attach the excel file, so i retype your table, and hope this works, put this on cell E3 and ENTERED as ARRAY FORMULA then copied down and cross to F3 :
=IFERROR(INDEX(CHOOSE({2\1},SUMIF($B$3:$B$18,$B$3:$B$18,$C$3:$C$18),A$3:A$18),MATCH(LARGE(IF(FREQUENCY(MATCH(SUMIF($B$3:$B$18,$B$3:$B$18,$C$3:$C$18)&$B$3:$B$18,SUMIF($B$3:$B$18,$B$3:$B$18,$C$3:$C$18)&$B$3:$B$18,),ROW($B$3:$B$18)-MIN(ROW($B$3:$B$18))+1),SUMIF($B$3:$B$18,$B$3:$B$18,$C$3:$C$18)+ROW($B$3:$B$18)/10000),ROWS($A$1:A1)),SUMIF($B$3:$B$18,$B$3:$B$18,$C$3:$C$18)+ROW($B$3:$B$18)/10000,),COLUMNS($A1:A1)),"")
And put this on G3 and copied down:
=IFERROR(SUMIFS($C$3:$C$18,$A$3:$A$18,$E3,$B$3:$B$18,$F3),"")
Hi Azumi,
I attached excel file having actual problem and report needed.
Thanks.
In K5, copied across and dnown:
=SUMIFS(D:D,$B:$B,$I5,$C:$C,$J5)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
@vishiabj
What happened when you tried to use the formulae given to you in post #6? Please don't expect any further help until you have (a) tried them and (b) reported back on any problems.
We help you, we don't do it all for you.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Am I missing something here? Picking up on a thread over 12 months on like we've not slept.
Hahaha. That's Covid lockdown for you!!
Imprisoned in Belfast....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks