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

hkelly393

New Member
Joined
Jun 25, 2019
Messages
9
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!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,651
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 25, 2019
Messages
9
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
Joined
Jun 25, 2019
Messages
9
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
Joined
Jun 25, 2019
Messages
9

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 10, 2011
Messages
4,651
Office Version
  1. 365
Platform
  1. Windows
Good catch Fluff.
@hkelly393, glad you got it worked out. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,930
Messages
5,545,083
Members
410,652
Latest member
Zot
Top