Sum of Column Based on Other Column

[sirsancho]

I have a sheet like this being used to track actors and characters lines for a record. Each line looks like so:

Draft	Record Date	Actor	Character	Page	Line #							Notes	# of Lines
Pilot BLUE 05/04/23		John Doe	John Smith	1	1	2	3	4	5				5
Pilot BLUE 05/04/23		John Doe	John Smith	2	6	8	10						3

Pilot BLUE 05/04/23		John Doe	John Smith	3	15	17							2
Pilot BLUE 05/04/23		John Doe	John Smith	4	19	20	22						3
Pilot BLUE 05/04/23		John Doe	John Smith	5	23	25							2
Pilot BLUE 05/04/23		John Doe	John Smith	8	45	49							2

I have intended to have a field under each actor which totals his lines from column "# of Lines" (N), I was hoping to make it dynamic/

I would need a formula (or other solution but I am no very familar with Macros and the like) that polls column "Actor" (D) but totals (N). I tried this but no luck:

=SUMIF(D:D,D1,N:N) Where D1 is the current Role in a static sell (likely in the same row as the sum field).

Is this possible?

Thanks

 

[kweaver]

Rather than the picture, would you post using XL2BB to make it easier to copy?

Would you provide a few examples of the solution(s) you expect?

 

[Dave Patton]

Ensure that you have numbers in column N

T202305a.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1								info
2				John Smith										9
3
4
5	Pilot BLUE 05/04/23		John Doe	John Smith	3	15	17							2
6	Pilot BLUE 05/04/23		John Doe	John Smith	4	19	20	22						3
7	Pilot BLUE 05/04/23		John Doe	John Smith	5	23	25							2
8	Pilot BLUE 05/04/23		John Doe	John Smith	8	45	49							2
9
1f

Cell Formulas
Range	Formula
N2	N2	=SUMIFS(N5:N1000,D5:D1000,D2)

 

[sirsancho]

Thanks, that worked a treat. Could I bother you to expand on that and ask one more question? In column B there is a sum area, say in row 9, is there a way for row B to show the previous non-blank value from row D? As rows get added and removed, I would like to make the formula relatively dynamic. I had a cell polling the 8th row, but if I delete that row for any reason it breaks the calc.

 

[Dave Patton]

You stated "In column B there is a sum area, say in row 9, is there a way for row B to show the previous non-blank value from row D? As rows get added and removed, I would like to make the formula relatively dynamic. I had a cell polling the 8th row, but if I delete that row for any reason it breaks the calc."

You may be able to use Excel to accomplish your objective. There are many tools and functions that can help.

One possibility is to use Data Subtotals to provide the totals. You will not have to enter the formulas. Please read the help information for the feature and review
examples.

The post shows the initial view and the view with the Subtotals. N.B. The view with all entries and the view with a grand total is also available.

T202305a.xlsm
	A	B	C	D	E	F	G	H
2	Heading1	Heading2	Name	Heading4	Heading5	Heading6	Heading7	Lines
3	Pilot BLUE 05/04/23	John Doe	John Smith	3	15	17		2
4	Pilot BLUE 05/04/23	John Doe	John Smith	4	19	20	22	3
5	Pilot BLUE 05/04/23	John Doe	John Smith	5	23	25		2
6	Pilot BLUE 05/04/23	John Doe	John Smith	8	45	49		2
7	Pilot BLUE 05/04/23		ABC					22
8
9
1ff

Heading1	Heading2	Name	Heading4	Heading5	Heading6	Heading7	Lines
		John Smith Total					9
		ABC Total					22
		Grand Total					31