Sum numbers based on multiple criteria

[Huizar]

Hello Excel Gurus,

I probably have a simplistic ask here, but I'm having trouble find the solution. Im trying to do a formula that allows the follwoing:

Sum numbers based on a key word "Incremental" and if Incremental then start the sum Until the month of July, all other regular words start the sum in January.

So Jon D would spread the the 50 accross Jan thru Dec at 4.16 per month, but Jon Doe would not sum until July and would spread from July thru Dec at 10 per month. I have a specific formula to run the spread, but i'm really trying to figure out when to start that count in July based on the word Incremental.

Employee	Status	Hours
Jon D	Various	50
Jon Doe	Incremental	120
Jon Do	Incremental	60
John Doooe	Various	45

any help would be greatly appreciated.

Thank You!!

 

[Alex Blakenburg]

Can you clarify. You use the word sum but everything else indicates you want to spread / allocate to months.
Do you have month columns to the right of what you are showing ?
Do you want the Hours divided by 12 and then populate either Jan - Dec or for incremental Jul - Dec ?
What columns are you using for the above and what columns for Jan - Dec ?

 

[Huizar]

Sorry for the confusion. You are right, i want to allocate to months. For example for Jon D is 50 hours total so with a formula to % distribution it allocates across the 12 months.

But for Jon Doe want the allocation of the 120 hours across July-through December or 6 months because he is an incremental employee.


My distribution formula looks like the following: Hours* (Pay Reg Days/Total Pay Reg Day) <<---This distributes it across the 12 months. The Pay Reg Days are the pay days for each month from Jan thru Dec.

With Jon Doe I want the distribution to start until July because he is an incremental employee

Hopefully this makes a bit more sense.

Thanks

 

[Alex Blakenburg]

It's 1am here.
Do you have months across the sheet and if so what are the columns eg letter for Jan & Dec ?

 

[Huizar]

It's 1am here.
Do you have months across the sheet and if so what are the columns eg letter for Jan & Dec ?

Hi Alex,

I can utilize months..so yes.

Thanks

 

[Alex Blakenburg]

I am afraid you are giving me very little to go by.
Step 1 of coming up with a formula is to have some sample data to work with which uses the same layout as you are using.
How the formula is going to work out where July starts will depend on whether your dates are text values or dates.
So all I can do is make lots of guesses on what the scenario might be.
Based on that, see below.

20210814 Formula Spreadbudget.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1					Pay Reg Days
2
3	Employee	Status	Hours	Total Pay Reg Days	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
4	Jon D	Various	50	261	21	20	23	22	21	22	22	22	22	21	22	23
5	Jon Doe	Incremental	120	261	21	20	23	22	21	22	22	22	22	21	22	23
6	Jon Do	Incremental	60	261	21	20	23	22	21	22	22	22	22	21	22	23
7	John Doooe	Various	45	261	21	20	23	22	21	22	22	22	22	21	22	23
8
9
10	Just pulling down from above				Formula Driven Result
11
12	Employee	Status	Hours	Total Pay Reg Days	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
13	Jon D	Various	50	261	4.02	3.83	4.41	4.21	4.02	4.21	4.21	4.21	4.21	4.02	4.21	4.41
14	Jon Doe	Incremental	120	261	0	0	0	0	0	0	10.11	10.11	10.11	9.66	10.11	10.57
15	Jon Do	Incremental	60	261	0	0	0	0	0	0	5.06	5.06	5.06	4.83	5.06	5.29
16	John Doooe	Various	45	261	3.62	3.45	3.97	3.79	3.62	3.79	3.79	3.79	3.79	3.62	3.79	3.97
17
Data

Cell Formulas
Range	Formula
D4:D7	D4	=SUM(E4:P4)
E4:P7	E4	=NETWORKDAYS(E$3,EOMONTH(E$3,0))
A13:D16	A13	=A4
E13:P16	E13	=ROUND(IF($B13<>"Incremental",$C4*E4/$D4,IF(MONTH(E$3)<7,0,$C4*E4/$D4)),2)

 

[Huizar]

I am afraid you are giving me very little to go by.
Step 1 of coming up with a formula is to have some sample data to work with which uses the same layout as you are using.
How the formula is going to work out where July starts will depend on whether your dates are text values or dates.
So all I can do is make lots of guesses on what the scenario might be.
Based on that, see below.

20210814 Formula Spreadbudget.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1					Pay Reg Days
2
3	Employee	Status	Hours	Total Pay Reg Days	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
4	Jon D	Various	50	261	21	20	23	22	21	22	22	22	22	21	22	23
5	Jon Doe	Incremental	120	261	21	20	23	22	21	22	22	22	22	21	22	23
6	Jon Do	Incremental	60	261	21	20	23	22	21	22	22	22	22	21	22	23
7	John Doooe	Various	45	261	21	20	23	22	21	22	22	22	22	21	22	23
8
9
10	Just pulling down from above				Formula Driven Result
11
12	Employee	Status	Hours	Total Pay Reg Days	Jan-21	Feb-21	Mar-21	Apr-21	May-21	Jun-21	Jul-21	Aug-21	Sep-21	Oct-21	Nov-21	Dec-21
13	Jon D	Various	50	261	4.02	3.83	4.41	4.21	4.02	4.21	4.21	4.21	4.21	4.02	4.21	4.41
14	Jon Doe	Incremental	120	261	0	0	0	0	0	0	10.11	10.11	10.11	9.66	10.11	10.57
15	Jon Do	Incremental	60	261	0	0	0	0	0	0	5.06	5.06	5.06	4.83	5.06	5.29
16	John Doooe	Various	45	261	3.62	3.45	3.97	3.79	3.62	3.79	3.79	3.79	3.79	3.62	3.79	3.97
17
Data

Cell Formulas
Range	Formula
D4:D7	D4	=SUM(E4:P4)
E4:P7	E4	=NETWORKDAYS(E$3,EOMONTH(E$3,0))
A13:D16	A13	=A4
E13:P16	E13	=ROUND(IF($B13<>"Incremental",$C4*E4/$D4,IF(MONTH(E$3)<7,0,$C4*E4/$D4)),2)

Alex,

this is exactly what I was looking for. Sorry about the clarity, but you hit the nail on the head! Your logic makes complete sense.

Thanks a million!!!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.