Sum an IndexMatch formula rather than return 1 result

[southike]

I have a range of data that I am using IndexMatch to get a result. It has two criteria: date and account number.

For most of them, I only need one result pulled. No issue. But I have one particular account number that is in the range more than one time, and I need the formula to return the total (sum) of them. This particular formula is in cell D9. I need it to find account number 90577371 and in the "Jan -21" column, return the sum. (Jan-21 column not shown since it's so far out to the right).

Thank you!

=INDEX($C$25:$BO$531,MATCH($C9,$C$25:$C$531,0),MATCH($C$2,$C$25:$BO$25,0))

Accrual Tracking 202101
Month-End	Jan-21
Description	Account No.	Accrual Sheet	Statement	Statement
Depreciation - Bank Building	90577390	$ 6,233.52		$ 6,233.52
Depreciation - Furn, Fix, and Eqiup	90577510	$ 4,511.80		$ 4,511.80
Amortization - Software	90577520	$ 4,845.21		$ 4,845.21
Real Estate Tax	90577370	$ 2,355.30		$ 2,355.30
Other Valuation Taxes	90577371	$ 5,964.29		$ 5,964.29
FF&E Personal Property Tax	90577570	$ 414.04	$ 414.04	$ -
Accrued Property Tax	90222976	$ 28,993.01	$ 211,327.43	$(182,334.42)	Previously-accrued
Depreciation, Amortization, Prop Tax, and Exam/Audit Fees
Description	Account No.	Period	Date Paid	Orig Amount	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18
A/D Main Office Build	90111702	2021	N/A	$ 31,383.09
A/D Green River Plaza Land	90111704	2021	N/A	$ 4,500.48
A/D Central Express Branch	90111706	2021	N/A	$ 3,378.81
A/D First St. Parking Lot	90111710	2021	N/A	$ 433.33
A/D 325 E Main St. Building	90111715	2021	N/A	$ 4,087.36
A/D 325 E Main St. Blacktop	90111717	2021	N/A	$ 1,666.67
A/D Green River Plaza Building	90111719	2021	N/A	$ 23,366.74
A/D 800 Cville Bypass Blacktop	90111748	2021	N/A	$ 1,961.00
A/D James Street Building	90111728	2021	N/A	$ 4,026.08
Total Accrued	90577390				-	-	-	-	-	-	-
Depreciation - Furniture, Fixtures, and Equipment
Description	Account No.	Period	Date Paid	Orig Amount	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18
A/D Furn, Fix, & Equip	90111722	2021	N/A	$ 54,141.27
Total Accrued	90577510				-	-	-	-	-	-	-
Amortization - Software
Description	Account No.	Period	Date Paid	Orig Amount	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18
Amortization - Software	90111726	2021	N/A	$ 44,555.47
Total Accrued	90577520				-	-	-	-	-	-	-
Accrued Property Tax
Description	Account No.	Period	Date Paid	Orig Amount	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18
REAL ESTATE TAX	90577370	2021	N/A	$ 28,263.38
CITY/CO FRANCHISE	90577371	2021	N/A	$ 71,571.48
STATE FRANCHISE 2019	90577371	2021	N/A	$ 243,112.56
FF&E PER PROP TAX	90577570	2021	N/A	$ 4,968.15
Total Accrued	90222976				-	-	-	-	-	-	-

 

[kweaver]

Welcome to Mr. Excel,

It's unclear (at least to me) what rows are what rows and where you are trying to analyze the data to produce the result.

 

[southike]

Thank you, kweaver. I will try and clarify:

My formula is in cell D9.

I want it to look in the following range: C25:BO531

I want it to look for account number 90577371 in this rage: C25:C531

Then, look in C25:CBO25 and find "Jan-21" (the date I've typed into cell C2)

And return the sum of whatever numbers it finds.

 

[southike]

Another way of saying (in my brain at least) is:

Look down until you find 90577371. Looks like I see it two times in a row. Now go accross until you're in the January 2021 column. Add the numbers up for that account number.

 

[kweaver]

Rather than entering a "-" in the cells where no data is available, use formatting to handle that because otherwise it's a character.
Then, suppose you use this in D5 and filled down?

=SUMPRODUCT((B5=$B$26:$B$57)*INDEX($F$26:$L$57,,MATCH($B$2,$F$25:$L$25,0)))

Of course, change the the column reference from "L" to however far you need. I just abbreviated the amount of sample data.

 

[southike]

That worked perfectly. Thanks so much!