Sum if Rows Match

[dhewett]

I have a table that records revenue by project (Column B). The table has two revenue types(Columns L & N) that feed a total revenue column (Column K). Each revenue type is broken out by project on a separate line. Some projects may only have revenue type, thus only one line, where others contain both types and require two lines.

How can I sum Columns L & N in Column from all rows that match Column B while also totaling single rows in Column K?

 

[Joe4]

Here is one way, for data on rows 2:9 (I wasn't going to recreate your whole sheet by hand, so just a sample of the first 8 rows).

Formula to put in cell C2 and copy down to C9:

=SUMPRODUCT(--(B$2:B$9=B2),(D$2:D$9+E$2:E$9))

Adjust to suit your range.

 

[felixstraube]

Would something like this work?:

Book1
	B										L	M	N	O	P	Q	R
1	Project										Revenue Type 1	Revenue Type 2
2	20-208										266,370.00
3	20-208											140,830.00
4	21-115										491,920.00
5	21-115											251,130.00
6	21-136										56,630.00
7	21-303										271,480.00
8	21-395										1,262,480.00
9	21-395											3,117,000.00
10
11														Project	Total Revenue	Revenue Type1	Revenue Type2
12														20-208	407,200.00	266,370.00	140,830.00
13														21-115	743,050.00	491,920.00	251,130.00
14														21-136	56,630.00	56,630.00	-
15														21-303	271,480.00	271,480.00	-
16														21-395	4,379,480.00	1,262,480.00	3,117,000.00
Sheet1

Cell Formulas
Range	Formula
O12:O16	O12	=UNIQUE(FILTER(B2:B20,B2:B20<>""))
P12:P16	P12	=SUM(FILTER($L$2:$M$20,$B$2:$B$20=O12))
Q12:Q16	Q12	=SUM(FILTER($L$2:$L$20,$B$2:$B$20=O12))
R12:R16	R12	=SUM(FILTER($M$2:$M$20,$B$2:$B$20=O12))
Dynamic array formulas.

 

[dhewett]

Here is one way, for data on rows 2:9 (I wasn't going to recreate your whole sheet by hand, so just a sample of the first 8 rows).

Formula to put in cell C2 and copy down to C9:

=SUMPRODUCT(--(B$2:B$9=B2),(D$2:D$9+E$2:E$9))

View attachment 104606

Adjust to suit your range.

That worked perfectly. Thank you!

 

[Joe4]

You are welcome.
Glad we were able to help!