Sum data if it meets criteria on different lines/columns

[jdramire]

Hello, this is my first post here but this seems to be a friendly place. I currently have data from our reporting software sending information that is helpful to read, but not to analyze. Right now I'm trying on another tab to calculate the field if it matches two criteria. Those are if the project name matches "362 - General Admission Operations" and if the line is "Total Revenues". This value that I'm looking for is highlighted below in green.

I can currently use a SumIfs formula to calculate, but only if I use a formula to copy "362 - General Admission Operations" to be inline with "Total Revenues". Thanks for any help you can provide happy to update if there are any questions. There are around 250 projects total and several reports to generate. Otherwise I would just copy this information and use the SumIfs.

 

[James006]

Hello,

With the assumption your Text is located in Column B .... and the targeted Amount is in Column G

=INDIRECT("G"&MATCH("Total Revenues",INDIRECT("B"&MATCH("362 - General Admission Operations",B:B,0)+1&":B100"),0)+MATCH("362 - General Admission Operations",B:B,0))

 

[Eric W]

Welcome to the MrExcel forum!

One option:

Book1
	A	B	C	D	E	F	G	H	I
1		362 - General Admission Operations							Sum of 362 - General Admission Operations
2	Revenues								$ 329,800.00
3
4	1234		Admissions Income				$ 300,000.00
5	12341234		Transaction Fee Revenue				$ 28,000.00
6
7		Total Revenues					$ 328,000.00
8
9		123 - Something else
10	Revenues
11
12	1234		xxx				$ 1,234.00
13	12341234		yyy				$ 5,678.00
14							$ 6,912.00
15		Total Revenues
16
17		362 - General Admission Operations
18	Revenues
19
20	1234		Admissions Income				$ 500.00
21	12341234		Transaction Fee Revenue				$ 600.00
22	2345		Bonus				$ 700.00
23		Total Revenues					$ 1,800.00
Sheet4

Cell Formulas
Range	Formula
I2	I2	=LET(f,FILTER(A1:G23,ISNUMBER(MATCH(B1:B23,{"362 - General Admission Operations","Total Revenues"},0))),s,SEQUENCE(ROWS(f)-1),SUM(IF(INDEX(f,s,2)="362 - General Admission Operations",INDEX(f,s+1,7))))

 

[jdramire]

You guys rock thanks for this answer. I mean it was so simple why didn't I think of it? I'll try both solutions and update if they work

 

[Eric W]

I wouldn't say it was especially simple. It definitely required using a few tricks that you might not have seen yet. One thing you should probably be aware of though. James006's suggestion will work if you only have 1 entry for 362. If you have 1 or more, then use mine. When you mentioned SUMIFS, I assumed more than 1 entry, but now I see that may not be the case.