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

#### hkelly393

##### New Member
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!

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### AhoyNC

##### Well-known Member
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
ABCDEF
1
2Test1 1,567.38 2,696.18 35,054.28 77,495.28
3Total Revenue 26,334.51 17,985.99 33,174.78
4Test2 23,991.03 4,546.22 26,199.89 77,495.28
Sheet1
Cell Formulas
RangeFormula
F2F2=SUM(INDEX(B2:D4,MATCH("Total Revenue",A2:A4,0),0))
F4F4=SUM(FILTER(\$B\$2:\$D\$4,\$A\$2:\$A\$4="Total Revenue",""))

#### hkelly393

##### New Member
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

##### New Member
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

##### New Member

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

#### Fluff

##### MrExcel MVP, Moderator
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

##### Well-known Member
Good catch Fluff.
@hkelly393, glad you got it worked out. Thanks for the feedback.

Replies
1
Views
185
Replies
0
Views
71
Replies
6
Views
167
Replies
1
Views
66
Replies
3
Views
153
Legacy 456155
L