Walker_Ice
Board Regular
- Joined
- Oct 6, 2023
- Messages
- 50
- Office Version
- 2021
- Platform
- MacOS
Hi everyone,
At the moment I have this spreadsheet which I am using to calculate the income services and business expenses per category. It works great but I have been struggling to make work how I need it to function exactly.
The table where all the services & expenses are summed by category, gives me all the data in the data range, regardless of the years. I would like to have a year selection button where I am able to select a year which I want to see the data for or be able to select "All" and have all the data summed up for all years.
I know that I need to use logic in completing this task but I am struggling to get this done. Can someone help??
Please and Thank you in advanced for all your support.
At the moment I have this spreadsheet which I am using to calculate the income services and business expenses per category. It works great but I have been struggling to make work how I need it to function exactly.
The table where all the services & expenses are summed by category, gives me all the data in the data range, regardless of the years. I would like to have a year selection button where I am able to select a year which I want to see the data for or be able to select "All" and have all the data summed up for all years.
I know that I need to use logic in completing this task but I am struggling to get this done. Can someone help??
Please and Thank you in advanced for all your support.
| TEsting_Excel_File.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | Income Services | Expenses | ||||||||||||
| 2 | Service | Date | Payment | Tips | Total | Item Description | Date | Quantity | Price | Total $ | Category | |||
| 3 | Medium-Long, Design, Gems | 4-Feb-21 | $85.00 | $0.00 | $85.00 | No C curve SITELEABY Nail Tips | 9/6/23 | 1 | $15.99 | $15.99 | Nail Prep | |||
| 4 | Short, Design, Gems | 13-Mar-23 | $75.00 | $25.00 | $100.00 | Halloween Construction | 9/10/22 | 1 | $1,400.00 | $1,400.00 | Construction | |||
| 5 | Short, Design, Gems | 17-Jun-22 | $75.00 | $25.00 | $100.00 | Kupa GelFinity - Soak Off Gel | 9/24/23 | 1 | $9.99 | $9.99 | Final Touches | |||
| 6 | Medium-Long, Design, Gems | 7-Jun-23 | $90.00 | $0.00 | $90.00 | PANA 100 Piece Nail Sanding Bands | 10/1/23 | 1 | $6.99 | $6.99 | Nail Prep | |||
| 7 | Medium-Long, Design, Gems | 10-Jul-23 | $95.00 | $0.00 | $95.00 | 50 Count Rectangle Nail Files | 10/1/23 | 1 | $18.99 | $18.99 | Shaping | |||
| 8 | Medium-Long, Design, Gems | 31-Mar-24 | $85.00 | $10.00 | $95.00 | Makartt Nail Rhinestone Glue | 10/1/23 | 1 | $6.86 | $6.86 | Nail Decorations | |||
| 9 | Freestyle | 26-May-23 | $70.00 | $10.00 | $80.00 | Young Nail Monomer | 10/5/23 | 1 | $30.56 | $30.56 | Acrylic Application | |||
| 10 | Medium, Design | 18-Feb-23 | $75.00 | $10.00 | $85.00 | NailEssentails White Gel Paint | 10/7/23 | 1 | $15.00 | $15.00 | Nail Art | |||
| 11 | Medium, Design, Gems | 1-Jun-23 | $60.00 | $10.00 | $70.00 | Halloween Ghost Wall-Decor | 10/11/24 | 1 | $9.41 | $9.41 | Decoration | |||
| 12 | Short, Design | 18-Feb-24 | $65.00 | $5.00 | $70.00 | Halloweenr | 10/11/24 | 1 | $13.60 | $13.60 | Decoration | |||
| 13 | Freestyle | 18-Mar-23 | $85.00 | $5.00 | $90.00 | 12 Color Luminous Spider Gel, Glow in The Dark | 10/11/24 | 1 | $19.11 | $19.11 | Nail Art | |||
| 14 | Medium, Design, Gems | 28-May-22 | $85.00 | $0.00 | $85.00 | Young Nail Monomer (6Fl Oz) | 10/16/23 | 1 | $30.56 | $30.56 | Acrylic Application | |||
| 15 | Medium, Design, Gems | 8-Feb-23 | $0.00 | $10.00 | $10.00 | AVOIN Halloween Spooky Banner | 10/12/23 | 1 | $10.61 | $10.61 | Decoration | |||
| 16 | Medium, Design, Gems | 3-Mar-23 | $0.00 | $10.00 | $10.00 | NO C curve SITELEABY Nail Tips | 10/29/23 | 1 | $15.99 | $15.99 | Nail Prep | |||
| 17 | Refill | 4-Jan-21 | $85.00 | $0.00 | $85.00 | Young Nails Nail Monomer (6Fl Oz) | 10/30/22 | 1 | $31.27 | $31.27 | Acrylic Application | |||
| 18 | Medium-Long, Design, Gems | 14-Feb-23 | $85.00 | $0.00 | $85.00 | Cordless Airbrush Kit | 11/4/23 | 1 | $32.99 | $32.99 | Nail Art | |||
| 19 | Short, Design | 1-Apr-23 | $65.00 | $5.00 | $70.00 | JODSONE 100 PCS Gel Nail Polish | 11/4/23 | 1 | $50.98 | $50.98 | Nail Art | |||
| 20 | Medium, Design | 3-Jan-22 | $95.00 | $0.00 | $95.00 | Young Nail Monomer (32 Fl Oz) | 11/4/21 | 1 | $94.99 | $94.99 | Acrylic Application | |||
| 21 | Medium, Design | 25-Feb-23 | $95.00 | $0.00 | $95.00 | 100 Pcs Mini Buffers 180/100 Grit | 11/4/23 | 1 | $9.99 | $9.99 | Shaping | |||
| 22 | Freestyle | 26-Jun-25 | $70.00 | $5.00 | $75.00 | Young Nails Swipe | 11/4/23 | 1 | $29.00 | $29.00 | Nail Prep | |||
| 23 | Medium, Design, Gems | 10-Mar-23 | $85.00 | $5.00 | $90.00 | Young Nails Protein Bond | 11/4/23 | 1 | $12.95 | $12.95 | Nail Prep | |||
| 24 | 50 Count Rectangle Nail Files | 11/4/23 | 1 | $18.99 | $18.99 | Shaping | ||||||||
| 25 | 6 Pieces Large Steel Nail Cutters | 11/5/23 | 1 | $13.99 | $13.99 | Nail Prep | ||||||||
| 26 | KUPA GelFinity Gel Topcoat (8Fl Oz) | 11/5/25 | 1 | $49.99 | $49.99 | Final Touches | ||||||||
| 27 | Business Expenses | Income Services | Aooba Nail Chrome Powder | 11/5/23 | 1 | $8.78 | $8.78 | Nail Art | ||||||
| 28 | Cuticle Prep | $0.00 | Clear Gel Manicure | $0.00 | Beetles Pearl Gel Polish | 11/5/24 | 1 | $15.99 | $15.99 | Nail Art | ||||
| 29 | Nail Prep | $94.91 | Gel Manicure | $0.00 | WOKOTO 6 Boxes Gold Metal Nail Charm Kit | 11/5/22 | 1 | $9.69 | $9.69 | Nail Decorations | ||||
| 30 | Acrylic Application | $187.38 | Freestyle | $245.00 | 160 Pcs Nail Swatches | 11/6/23 | 4 | $8.99 | $35.96 | Nail Art | ||||
| 31 | Shaping | $47.97 | Short, Design | $140.00 | ||||||||||
| 32 | Nail Art | $178.81 | Short, Design, Gems | $200.00 | ||||||||||
| 33 | Nail Decorations | $16.55 | Medium, Design | $275.00 | Select Year: | |||||||||
| 34 | Final Touches | $59.98 | Medium, Design, Gems | $265.00 | All | |||||||||
| 35 | Sanitizaton | $0.00 | Medium-Long, Design | $0.00 | ||||||||||
| 36 | Photography | $0.00 | Medium-Long, Design, Gems | $450.00 | ||||||||||
| 37 | Storage | $0.00 | Long, Design | $0.00 | ||||||||||
| 38 | Decoration | $33.62 | Long, Design, Gems | $0.00 | ||||||||||
| 39 | Rent | $0.00 | XXL, Design | $0.00 | ||||||||||
| 40 | Furniture | $0.00 | XXL, Designs, Gems | $0.00 | ||||||||||
| 41 | Construction | $1,400.00 | Refill | $85.00 | ||||||||||
| 42 | Total Expenses | $2,019.22 | Total Income | $1,660.00 | ||||||||||
| 43 | ||||||||||||||
Sheet1 | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E3:E23 | E3 | =SUM(C3:D3) |
| B28:B41 | B28 | =SUMIF($L$3:$L$30,A28,$K$3:$K$30) |
| B42,D42 | B42 | =SUM(B28:B41) |
| D28:D41 | D28 | =SUMIF($A$3:$A$23,C28,$E$3:$E$23) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| F34 | List | All,2021,2022,2023,2024,2025 |
