+ Reply to Thread
Results 1 to 18 of 18

Costs Category Filter with Totals

  1. #1
    Registered User
    Join Date
    02-19-2020
    Location
    SE-Asia
    MS-Off Ver
    2019
    Posts
    85

    Costs Category Filter with Totals

    First of all, Everyone a Happy and Healthy 2023!!

    For an upcoming Camper Trip (June )

    I would like to keep a list of expenses

    You see several categories

    - Transport
    - Food
    - Shopping
    - Going Out
    - Groceries
    - Fines
    - Petrol
    (going to add more for sure )

    What I want is that I select a category (drop down?) and then will see ALL the Rows for that category and also see a total amount spend on that Category

    Any questions just ask

    Please Note: The Color coding is just to show the several categories to make it a bit clearer in the sample sheet. The Color Coding Can go (will not use it)
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Costs Category Filter with Totals

    List the categories, sort them alphabetically, if desired, somewhere. They're in Sheet 1 A1.

    Use this Named Range (Category) to select the entire list DYNAMICALLY:

    =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("Zzzz",Sheet1!$A:$A))

    Set Data Validation = Category in B3. In B4:

    =SUMIF(E:E,B3,G:G)

    Fines??? Some holiday you must be planning!!!
    Attached Files Attached Files
    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

  3. #3
    Registered User
    Join Date
    02-19-2020
    Location
    SE-Asia
    MS-Off Ver
    2019
    Posts
    85

    Re: Costs Category Filter with Totals

    hahahahahaahhaa

    I am going with a camper and thus loading too much is easy as I am limited to 3,5T only
    (Note to self: Need to loose some weight myself also )

    Thx Glenn

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Costs Category Filter with Totals

    We live in a camper for 6 months each year since 2015... (apart from during The Plague) also 3.5 tonne... but no fines, so far!! I could also usefully lose about 6 kg.

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

  5. #5
    Registered User
    Join Date
    02-19-2020
    Location
    SE-Asia
    MS-Off Ver
    2019
    Posts
    85

    Re: Costs Category Filter with Totals

    We are going through AT and CH and they are very very strict especially in the summer months.

    Ps: It's a rental, but first prio is (when we pick it up) go to a weighing station

  6. #6
    Registered User
    Join Date
    02-19-2020
    Location
    SE-Asia
    MS-Off Ver
    2019
    Posts
    85

    Re: Costs Category Filter with Totals

    Can I request one more feature?

    Only show the rows that correspond with the category?

    So when selecting "Fines" only show ALL the lines with the Fine dates

    Edit: And then also show ONLY the SUMS for that Category (and of course the Total amount )
    Last edited by iammike; 01-07-2023 at 11:00 PM.

  7. #7
    Registered User
    Join Date
    02-19-2020
    Location
    SE-Asia
    MS-Off Ver
    2019
    Posts
    85

    Re: Costs Category Filter with Totals

    I think I need to do this with Pivot Tables, but I have NO clue on how to do it

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Costs Category Filter with Totals

    No, not Pivots. Nasty dead things.

    1. Move the summary to a new sheet.

    2. Lose the blank columns (they only interfere with formula copying).

    3. In B7, copied across & down:

    =IFERROR(INDEX(Costs!A:A,AGGREGATE(15,6,ROW(Costs!$A$2:$A$95)/(Costs!$C$2:$C$95=$A$4),ROWS(B$7:B7))),"")
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-19-2020
    Location
    SE-Asia
    MS-Off Ver
    2019
    Posts
    85

    Re: Costs Category Filter with Totals

    Wow that is just brilliant!!

    I wished I could spread some more reps to you.

    But you have to do it (for now) with

    thank you thank you thank you thank you

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Costs Category Filter with Totals

    Quote Originally Posted by iammike View Post
    We are going through AT and CH and they are very very strict especially in the summer months.
    We're usually in France or Spain and have never been checked. Minus the camper, we're heading in your general direction... to SE Asia next week... to get away from the dreary Irish winter. Cold, grey and wet...

  11. #11
    Registered User
    Join Date
    02-19-2020
    Location
    SE-Asia
    MS-Off Ver
    2019
    Posts
    85

    Re: Costs Category Filter with Totals

    It's nice and cool here (for you guys not for me as I sleep with a blanket hahahahaha) (nights around 13c-21c) and days max temp around 33c

    My wife (Thai) loves mountains so it will be a lot of S-DE then AT, IT and CH (Grossglockner, Simplon etc etc)

    Going for 56 days with the camper (hope that is enough )

    Thx again, appreciated. (But I think I will hate the results from the calculations hahahahahaha)

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Costs Category Filter with Totals

    Europe's not cheap... but the £ to THB is making our trip more expensive than previously.

  13. #13
    Registered User
    Join Date
    02-19-2020
    Location
    SE-Asia
    MS-Off Ver
    2019
    Posts
    85

    Re: Costs Category Filter with Totals

    The THB is tanking towards other countries previously I was getting 37+ -> EUR but it now has gone down to 35.

    And also please NOTE: New Regulations to Enter TH from the 9th on-wards

    https://thepattayanews.com/2023/01/0...m-january-9th/

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Costs Category Filter with Totals

    Not a problem. Fully vaccinated (4 shots in total), last boosted in November 2022. I did eventually get Covid, about a year back, but it was only really unpleasant for 24 hours. I even stopped eating. That's unheard of, normally.

  15. #15
    Registered User
    Join Date
    02-19-2020
    Location
    SE-Asia
    MS-Off Ver
    2019
    Posts
    85

    Re: Costs Category Filter with Totals

    Enjoy your trip

  16. #16
    Registered User
    Join Date
    02-19-2020
    Location
    SE-Asia
    MS-Off Ver
    2019
    Posts
    85

    Re: Costs Category Filter with Totals

    Quote Originally Posted by Glenn Kennedy View Post
    No, not Pivots. Nasty dead things.

    1. Move the summary to a new sheet.

    2. Lose the blank columns (they only interfere with formula copying).

    3. In B7, copied across & down:

    =IFERROR(INDEX(Costs!A:A,AGGREGATE(15,6,ROW(Costs!$A$2:$A$95)/(Costs!$C$2:$C$95=$A$4),ROWS(B$7:B7))),"")
    I have another question regarding this WB, which works perfectly.

    For example Sheet 2 $A$4 = Food but I want in a different WB to have the Total for Petrol (so a different value as in $A$4) is that possible?

  17. #17
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,341

    Re: Costs Category Filter with Totals

    Just change the value of A4 and you get the total of something else.
    No formula change is necessary.

  18. #18
    Registered User
    Join Date
    02-19-2020
    Location
    SE-Asia
    MS-Off Ver
    2019
    Posts
    85

    Re: Costs Category Filter with Totals

    Thx @Hans

    But that would mean if I change the Value in A4 to something else, then in the other WB the value (for that specific category) then will not be listed anymore.

    I have to re-think this, maybe it's better to do it in the same WB and not spread it out to 2 different WB's as I was planning to do

    Thx

    Edit: I just thought of it and could do SUMIF and then the category from the other WB.
    Last edited by iammike; 09-05-2023 at 01:40 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to filter data by category and delete rows where category sums up to zero
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-02-2021, 12:57 PM
  2. Replies: 4
    Last Post: 02-10-2019, 04:36 PM
  3. Category Totals Using Lists
    By Liberty42 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-17-2013, 09:34 PM
  4. Replies: 2
    Last Post: 07-06-2012, 02:22 AM
  5. how to generate report based on adding costs of particular category
    By hoser in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-20-2007, 06:58 PM
  6. [SOLVED] Adding category totals
    By [email protected] in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2006, 06:50 PM
  7. Replies: 0
    Last Post: 02-28-2006, 05:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1