Find value in column A, Add values from matching row in columns B, C, D

[hkelly393]

I have a Profit and Loss statement where I want to be able to find "Total Revenue" from column A and then add the values from the matching row that appear in columns B, C and D. The P&L is its own tab and the values are pulled into a Summary tab in the same workbook.

This is what I have for pulling a single value but I can't seem to figure out how to pull multiple values and add them together. The idea is to add B, C, D because they represent Q3. The row in which "Total Revenue" appears changes across companies so I can't grab it from say a static Row 35 always. I need to find the text and then grab the values from the row, which as I mentioned changes from company to company.

=SUMIF('ARL Q3'!$A:$A, " Total Revenue", 'ARL Q3'!N:N) - works great for one value. but I can't get N:N to read B:D for example. It pulls value from B but not C and D, naturally...I need it to add all three columns. I'm sure there's a way that I just can't think my around.
Thanks!

 

[AhoyNC]

Maybe something like one of the formulas below.
It helps to let us know which version on Excel you are running.
If you have Excel 356 with the FILTER function try the formula in F4, if not then the formula in F2.

Book1
	A	B	C	D	E	F
1
2	Test1	1,567.38	2,696.18	35,054.28		77,495.28
3	Total Revenue	26,334.51	17,985.99	33,174.78
4	Test2	23,991.03	4,546.22	26,199.89		77,495.28
Sheet1

Cell Formulas
Range	Formula
F2	F2	=SUM(INDEX(B2:D4,MATCH("Total Revenue",A2:A4,0),0))
F4	F4	=SUM(FILTER($B$2:$D$4,$A$2:$A$4="Total Revenue",""))

 

[hkelly393]

365! i forgot to mention that. i'll give these a try! thanks. I played around with index but couldn't get it to work.

 

[hkelly393]

neither of these work for me. something about the data being on another sheet maybe? my references for the sheet are right.
=SUM(FILTER('ARL Q3'!$B$2:$D$4,'ARL Q3'!$A$2:$A$4="Total Revenue",""))

this one gives me a #VALUE error.

The other says N/A

still playing around with it.

 

[hkelly393]

Nope. I'm just dumb! I've got it. Thank you so much!!

 

[Fluff]

I would suggest changing the "" at the end of the formula to 0, otherwise you will get an error if the filter returns nothing.

 

[AhoyNC]

Good catch Fluff.
@hkelly393, glad you got it worked out. Thanks for the feedback.