excel formula for 20% of the sum of column

[lichldo]

hello, is this possible?

I have a bunch of numbers in a column, lets say its from I4:I20
Now in I21, I want to get 20% of the sum of I4:I20 - is that possible with a formula?

Now if yes, a potential added complexity... the range of cells will actually be different every time. BUT the constant will be that I always want to do 20% of the sum that is above a cell in column H that will say "Project Management"
So it's reading a value from column H to get the 20% of the sum of column I in everything thats above that cell in column H

FOR EXAMPLE ---

	H	I
4	Copy Editing
5	- Copywriting
6	- Copywriting
7	- Creative Director - Concepts
8	Freelancer
9	Creative Design
10	- Creative Senior -Art Direction	8
11	- Creative Director - Concepts
12	- Creative Director - Concepts	2
13	- Creative Mid - Design & Amends
14	- Production Manager	1
15	Design Development
16	- Creative Senior -Art Direction
17	- Creative Senior -Art Direction
18	- Creative Mid - Design & Amends	4
19	Artwork
20	- Creative Artworker	4
21	Project Management
22	- Account Director
23	- Group Account Director
24	- Project Manager Senior
25

Please note that "Project Manager" may be in a different row every time. So I cannot base it on the face that it's in H21, and want to base it on the text itself

 

[Fluff]

How about

Fluff.xlsm
	H	I
3	H	I
4	Copy Editing
5	- Copywriting
6	- Copywriting
7	- Creative Director - Concepts
8	Freelancer
9	Creative Design
10	- Creative Senior -Art Direction	8
11	- Creative Director - Concepts
12	- Creative Director - Concepts	2
13	- Creative Mid - Design & Amends
14	- Production Manager	1
15	Design Development
16	- Creative Senior -Art Direction
17	- Creative Senior -Art Direction
18	- Creative Mid - Design & Amends	4
19	Artwork
20	- Creative Artworker	4
21	Project Management
22	- Account Director
23	- Group Account Director
24	- Project Manager Senior
25		3
Data

Cell Formulas
Range	Formula
I25	I25	=SUM(I4:INDEX(I:I,MATCH("project management",H4:H50,0)))*0.2

 

[lichldo]

How about

Fluff.xlsm
	H	I
3	H	I
4	Copy Editing
5	- Copywriting
6	- Copywriting
7	- Creative Director - Concepts
8	Freelancer
9	Creative Design
10	- Creative Senior -Art Direction	8
11	- Creative Director - Concepts
12	- Creative Director - Concepts	2
13	- Creative Mid - Design & Amends
14	- Production Manager	1
15	Design Development
16	- Creative Senior -Art Direction
17	- Creative Senior -Art Direction
18	- Creative Mid - Design & Amends	4
19	Artwork
20	- Creative Artworker	4
21	Project Management
22	- Account Director
23	- Group Account Director
24	- Project Manager Senior
25		3
Data

Cell Formulas
Range	Formula
I25	I25	=SUM(I4:INDEX(I:I,MATCH("project management",H4:H50,0)))*0.2

Thank you, that works great.

Do you know, would there be a way to also have it exclude any hours in column I associated with " - Production Manager" in column H?

 

[Fluff]

How about

=SUMIFS(I4:INDEX(I:I,MATCH("project management",H1:H50,0)),H4:INDEX(H:H,MATCH("project management",H1:H50,0)),"<>- production manager")*0.2