excel formula for 20% of the sum of column

lichldo

Board Regular
Joined
Apr 19, 2022
Messages
65
Office Version
  1. 365
Platform
  1. MacOS
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 ---

HI
4Copy Editing
5- Copywriting
6- Copywriting
7- Creative Director - Concepts
8Freelancer
9Creative Design
10- Creative Senior -Art Direction8
11- Creative Director - Concepts
12- Creative Director - Concepts2
13- Creative Mid - Design & Amends
14- Production Manager1
15Design Development
16- Creative Senior -Art Direction
17- Creative Senior -Art Direction
18- Creative Mid - Design & Amends4
19Artwork
20- Creative Artworker4
21Project 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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
How about
Fluff.xlsm
HI
3HI
4Copy Editing
5- Copywriting
6- Copywriting
7- Creative Director - Concepts
8Freelancer
9Creative Design
10- Creative Senior -Art Direction8
11- Creative Director - Concepts
12- Creative Director - Concepts2
13- Creative Mid - Design & Amends
14- Production Manager1
15Design Development
16- Creative Senior -Art Direction
17- Creative Senior -Art Direction
18- Creative Mid - Design & Amends4
19Artwork
20- Creative Artworker4
21Project Management
22- Account Director
23- Group Account Director
24- Project Manager Senior
253
Data
Cell Formulas
RangeFormula
I25I25=SUM(I4:INDEX(I:I,MATCH("project management",H4:H50,0)))*0.2
 
Upvote 0
How about
Fluff.xlsm
HI
3HI
4Copy Editing
5- Copywriting
6- Copywriting
7- Creative Director - Concepts
8Freelancer
9Creative Design
10- Creative Senior -Art Direction8
11- Creative Director - Concepts
12- Creative Director - Concepts2
13- Creative Mid - Design & Amends
14- Production Manager1
15Design Development
16- Creative Senior -Art Direction
17- Creative Senior -Art Direction
18- Creative Mid - Design & Amends4
19Artwork
20- Creative Artworker4
21Project Management
22- Account Director
23- Group Account Director
24- Project Manager Senior
253
Data
Cell Formulas
RangeFormula
I25I25=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?
 
Upvote 0
How about
Excel Formula:
=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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top