Excel Spread Sheet Budgeting

The date ranges don’t work consistently if you put both dates in the same column, but do work if you create seperate column, one for the start date and the other for an end date. Both of these should still be named in the header the exact same name as the column with the dates we are interested in (in this case the header name is “Trans. Date”) The instructions are below on the Excel sheet, just put the answers in the boxes next to them and fill in 11-15 as well.

Statement ending March 23, 2022
Example For Excel Modules 2-6
Address
(859)xxx-xxxx
Account Ending in XXXX
Transactions
Trans. date
Post date
2/25/2022
2/25/2022
3/4/2022
3/4/2022
3/5/2022
3/5/2022
3/8/2022
3/10/2022
3/11/2022
3/11/2022
3/11/2022
3/12/2022
3/12/2022
3/12/2022
3/12/2022
3/12/2022
3/12/2022
3/13/2022
3/13/2022
3/13/2022
3/14/2022
3/16/2022
3/21/2022
3/23/2022
3/23/2022
3/4/2022
3/4/2022
3/5/2022
3/5/2022
3/8/2022
3/10/2022
3/18/2022
3/11/2022
3/11/2022
3/12/2022
3/12/2022
3/12/2022
3/12/2022
3/12/2022
3/12/2022
3/13/2022
3/13/2022
3/13/2022
3/14/2022
3/16/2022
3/21/2022
3/23/2022
3/23/2022
#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Description
GOOGLE *GOOGLE STORAGE 855-836-3987
LIQUOR WORLD RICHMOND KY
PARKMOBILE 770-818-9036 GAAPPLE PAY ENDING IN
APPLE.COM/BILL 866-712-7753
APPLE.COM/BILL 866-712-7753
WASTE MGMT WM EZPAY 866-834-2080 TX
COOK OUT RICHMOND KY RICHMOND KY
TST* AL’S BAR LEXINGTON KY
TST* AL’S BAR LEXINGTON KY
TST* LUNA IN LEXINGTON LEXINGTON KY
CLICKUP 888-625-4258 CA
COOK OUT RICHMOND KY RICHMOND KY
SPEEDWAY 09593 LEXINGTON LEXINGTON KY
TST* AL’S BAR LEXINGTON KY
TST* LUNA IN LEXINGTON LEXINGTON KY
WAFFLE HOUSE 0683 RICHMOND KY
CASA FIESTA MEXICAN GRIL RICHMOND KY
MCDONALD’S F2100 RICHMOND KY
O CHARLEYS 241 RICHMOND KY
THAI SMILE – RICHMOND KY RICHMOND KY
TAPENADA T1 ORD CHICAGO IL2020
PARKMOBILE 770-818-9036 GAAPPLE PAY ENDING IN
INTEREST CHARGE ON PROMOTIONAL PURCHASES
INTEREST CHARGE ON PURCHASES
Instructions
What are the Total Purchases for all Records? (D14:D37)
What are the Total Restaurant Purchases? Use SumIF function to add all matching transactions for
“Restaurants”
What are the Total Services Purchases? Use SumIF function to add all matching transactions for “Services”
What are the total APPLE.COM purchases? Use SumIF function to at any row with “APPLE*” in the Descriptions
What is the total Interest AND Services Category Records? Use SUMIF +SUMIF Function
Format A13:E37 as a Table. Name the Table Credit_Card_1. Sort the records by Date. What is the oldest
transaction date?
Filter Records to only include transaction dates in LUNA IN LEXINGTON KY only. How many records are in the
filtered table?
Remove all filters and Conditionally Format Table so top 10% transaction Amounts is in red. Sum those
Amounts.
Add a total row to the table, add a function to sum the Amounts and Count the records in the Category
“=SUBTOTAL(103,[Category])”. How many records are in the table? Record the value NOT the cell reference in
the answer column.
Add a Subtotal to the Amount, What is the Subtotal Amount for all the table records? Record the value NOT
the cell reference in the answer column.
Use the DSUM and Criteria to find the times the Credit card purchased from Cook Out
Use the DSUM and Criteria to find the times the Credit card purchased from Casa fiesta AND Cook Out
Use the DSUM and Criteria to SUM the transactions with a transaction date matching or between 3/1/2022 3/11/2022
Use the DSUM and Criteria to find the times the Credit card ALL purchases from 3/5/2022 to 3/15/2022 the
Descriptions for APPLE*, PARKMOBILE*, and Interest*
Use the DSUM and Criteria to SUM the transactions matching the Category of “Restaurants” and between
3/12/2023 – 3/20/22.
Amount
Category
1.99 Merchandise
38.15 Merchandise
1.75 Services
16.95 Merchandise
2.99 Merchandise
35.79 Services
7.3 Restaurants
10 Restaurants
47 Restaurants
16.12 Restaurants
39.9 Merchandise
6.67 Restaurants
40.35 Gasoline
16 Restaurants
17.64 Restaurants
10.5 Restaurants
39.2 Restaurants
2.12 Restaurants
22.12 Restaurants
18.97 Restaurants
18.75 Restaurants
1.75 Services
0.11 Interest
5.08 Interest
11 Criteria
Description
COOK OUT RICHMOND KY
RICHMOND KY
12 Criteria
Description
13 Criteria
Trans. date
14 Criteria
Trans. date
x
x
15 Criteria
Trans. date
Output Function
Trans. date