Sum data with multiple criteria

Katich

Board Regular
Joined
Jan 22, 2008
Messages
156
Office Version
  1. 2019
Platform
  1. Windows
Hello,
I know how to use the SUMIFS but for what i want to use it for, i feel i may need something else. My first sheet i have column D (MTD Actual) which i want to Sum all amounts that equal the account in column C and also reference the entity (B1). I'm able to get that to work. Now, i want to have in the formula so that whenever the month (B2) changes, it will find the column on my other tab and pull those totals. This way i don't need to change the lookup column each month.

Book2
ABCDEFG
1Entity5860-xxxx
2MonthJan/25
3Roll UpReporting LineAccount DescriptionMTD ActualAdjustmentsBudgetVariance
4Operating RevenueSponsorship Revenue401001-Sponsorship Income--
5Operating RevenueOther Revenue401003-Non-Tenant Income--
6Operating RevenueRental Revenue401004-Lease Income--
7Operating RevenueOther Revenue401010-Other/Misc Income83,858.9083,858.90
8Operating RevenueOther Revenue401030-Parking Revenue--
9Operating RevenueFood & Beverage Revenue401040-Banquet / Catering Revenue--
10Operating RevenueRental Revenue401100-Tenant Base Rent--
11Operating RevenueReimbursements401200-Tenant CAM--
12Operating RevenueReimbursements401300-Tenant Insurance--
13Operating RevenueReimbursements401400-Tenant Real Estate Tax--
14Operating RevenueOther Revenue401500-Tenant Percentage Rent--
15Operating RevenueReimbursements401600-Tenant TDD Tax--
16Operating RevenueReimbursements401700-Tenant CAT Tax--
17Operating RevenueRental Revenue401800-Tenant Marketing Fee--
18Operating RevenueReimbursements401900-Tenant Utility Reimbursement--
19Operating RevenueTicket Revenue402001-Ticket Sales Revenue--
20Operating RevenuePremium Seating402010-Luxury Suites--
DT
Cell Formulas
RangeFormula
D4:D20D4=SUMIFS('Income Statement - Accou'!$D$3:$D$1000,'Income Statement - Accou'!$A$3:$A$1000,DT!$B$1,'Income Statement - Accou'!$C$3:$C$1000,DT!C4)*-1
G4:G20G4=D4+E4-F4



Book2
ABCDE
1Income Statement - Account Balances - Report
2EntityDepartmentAccountJan/25Feb/25
435860-XXXX000-Default Department401010-Other/Misc Income(83,858.90)0.00
445860-XXXX000-Default Department502044-Propane/Gasoline(127.74)0.00
455860-XXXX000-Default Department505233-Food and Beverage Supplies(415.31)0.00
465860-XXXX000-Default Department508001-Retail Non-CAM Operating Expenses5,352.83(12,612.95)
475860-XXXX000-Default Department601031-Employee expense reimbursements(2,838.93)458.73
485860-XXXX000-Default Department601040-Payroll Taxes - Social Security63.020.00
495860-XXXX000-Default Department602055-Office Supplies74.540.00
505860-XXXX000-Default Department602070-Bank Service Charges1,360.720.00
515860-XXXX000-Default Department602086-Employee Benefits9,461.640.00
525860-XXXX000-Default Department700022-Amortization Expense8,388.000.00
Income Statement - Accou
 
How about
Excel Formula:
=-SUMIFS(INDEX('Income Statement - Accou'!$D$3:$Z$1000,,MATCH(b2,'Income Statement - Accou'!$D$2:$Z$2,0)),'Income Statement - Accou'!$A$3:$A$1000,$B$1,'Income Statement - Accou'!$C$3:$C$1000,C4)
 
Upvote 0
Try.
Excel Formula:
D4
=SUMIFS(INDEX('Income Statement - Accou'!$D$3:$E$1000,0,MATCH($B$2,'Income Statement - Accou'!$D$2:$E$2,0)),'Income Statement - Accou'!$A$3:$A$1000,DT!$B$1,'Income Statement - Accou'!$C$3:$C$1000,DT!C4)*-1
Copy it downward.
 
Upvote 0
Solution
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top