Multiple conditions using if statement and and/or

[sadonnie]

I am sure I just having something backwards in my logic, but I could use some assistance with an expert's opinion and suggestion. I have a list of names of when each person was on-boarded (1 means they were on-boarded and 0 means they have not been on-boarded) month over month. If you notice in the "Data" table below, Roger was onboarded in February. The "Output w/ Formula" is how I want to the end result to look. I want Jan to be 0 since he did not onboard then, a 1 value for Feb since he on-boarded then, and a 0 for March since he is already on-boarded. The outcome I am trying to achieve is once a person is on-boarded do not count them again, even though preceding month's will have a 1 value (just count them the first time a 1 appears in the given month).

Appreciate the help!

Here are my formulas for Col F, G, H (I'm sure I can combine to one If statement:
Col F: =IF(C2>0,1,0)
Col G: =IF(OR(AND(C2>0,D2>0),AND(C2=0,D2=0)),0,1)
Col H: =IF(OR(AND(C3>0,D3>0,E3>0),AND(C3=0,D3=0,E3=0)),0,1)

My challenge is the formula in Col H: =IF(OR(AND(C3>0,D3>0,E3>0),AND(C3=0,D3=0,E3=0)),0,1)

This formula (in Col H) works for Mary, but not Roger.

 

[KRice]

How would one know when each individual was first on-boarded? That information is not shown in your table.

 

[jasonb75]

Long nested IF's are a trap that is easily fallen into, there are often much easier ways.

Book2 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Name	Jan	Feb	Mar		BOG as of Jan	BOG as of Feb	BOG as of Mar
2	Roger	0	1	1		0	1	0
3	Mary	1	1	1		1	0	0
4	Scott	0	0	1		0	0	1
Sheet4

Cell Formulas
Range	Formula
F1:H1	F1	="BOG as of "&B1
F2:H4	F2	=--(COUNTIF($B2:B2,1)=1)

 

[KRice]

I was thinking along similar lines, but wondered why the initial table was necessary, especially when it held information that was not desired (the extra 1's). One option would look something like this, to tabulate the on-board month-year followed by a visual representation:

MrExcel20210401.xlsx
	A	B	C	D	E	F
8	Name	Onboarded		Jan-21	Feb-21	Mar-21
9	Roger	Feb-21		0	1	0
10	Mary	Jan-21		1	0	0
11	Scott	Mar-21		0	0	1
Sheet5

Cell Formulas
Range	Formula
D9:F11	D9	=--($B9=D$8)

 

[sadonnie]

Long nested IF's are a trap that is easily fallen into, there are often much easier ways.

Book2 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Name	Jan	Feb	Mar		BOG as of Jan	BOG as of Feb	BOG as of Mar
2	Roger	0	1	1		0	1	0
3	Mary	1	1	1		1	0	0
4	Scott	0	0	1		0	0	1
Sheet4

Cell Formulas
Range	Formula
F1:H1	F1	="BOG as of "&B1
F2:H4	F2	=--(COUNTIF($B2:B2,1)=1)

Thanks @jasonb75 that is much simpler and works as expected. And I learned (as a bonus) that the double hyphen you used coerces TRUE or FALSE values to their numeric equivalents, 1 and 0.

 

[sadonnie]

@KRice unfortunately the master data provided is not in an easy format and the initial table is an pivot table I had to create to get to my final desired step. If there is a way to go through the master data tab and find the 1st occurrence (based on when they charged actual hours to a project) that would eliminate my pivot table and the "extra" step. But keep in mind, when someone onboards, they might charge (ex. Feb) to 2 projects in the same 1st occurrence (aka month). Sample Master Data:

 

[sadonnie]

@jasonb75 I guess I forgot to mention that the number could be greater than 1 (b/c that number can increase b/c of multiple projects that the person on-boarded to).

So now Mary onboarded in Jan and hit 1 project, but in Feb and March she hit 2 projects, so the formula does not handle this case for this situation.

See the table:

 

[jasonb75]

Simple fix (if I've guessed correctly).

Book2 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	Name	Jan	Feb	Mar		BOG as of Jan	BOG as of Feb	BOG as of Mar
2	Roger	0	1	1		0	1	0
3	Mary	1	2	2		1	0	0
4	Scott	0	0	1		0	0	1
Sheet4

Cell Formulas
Range	Formula
F1:H1	F1	="BOG as of "&B1
F2:H4	F2	=--(COUNTIF($B2:B2,">0")=1)

If there is a way to go through the master data tab and find the 1st occurrence (based on when they charged actual hours to a project) that would eliminate my pivot table and the "extra" step.

It would help if you updated your Account details to show us which version of excel that you are using so that we know what features you have available to use. There is a possibility that the intermediate steps could be excluded, but you would need to confirm the expected results when there are numbers other than 1 or 0 shown in the table. Should it still just show a 1 for the first entry as in the table that I have posted above?

 

[KRice]

Here is another idea if you'd prefer to skip the pivot table step...this operates on the master list and matches person's name (must be exact), finds which rows have non-zero actual hours, and then returns the earliest date. You may have to format the "Onboarded" column to display month-year:

MrExcel20210401.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Onboarded		Dec-20	Jan-21	Feb-21	Mar-21		Employee	Mo Name	Year	Actual Hrs	Project
2	Smith; Roger	Feb-21		0	0	1	0		Smith; Roger	March	2021	40	A
3	Smith; Mary	Dec-20		1	0	0	0		Smith; Roger	April	2021	0	A
4									Smith; Roger	May	2021	0	A
5									Smith; Roger	June	2021	0	A
6									Smith; Roger	July	2021	0	A
7									Smith; Roger	August	2021	0	A
8									Smith; Roger	September	2021	0	A
9									Smith; Roger	October	2021	0	A
10									Smith; Roger	November	2021	0	A
11									Smith; Roger	February	2021	142	B
12									Smith; Roger	March	2021	80	B
13									Smith; Roger	April	2021	0	B
14									Smith; Roger	May	2021	0	B
15									Smith; Roger	June	2021	0	B
16									Smith; Roger	July	2021	0	B
17									Smith; Mary	Dec	2021	10	A
18									Smith; Mary	Dec	2020	20	C
19									Smith; Mary	July	2021	30	C
Sheet5

Cell Formulas
Range	Formula
D2:G3	D2	=--($B2=D$1)
B2:B3	B2	=AGGREGATE(15,6,DATEVALUE($J$2:$J$19&" 1, "&$K$2:$K$19)/(($L$2:$L$19<>0)*($I$2:$I$19=$A2)),1)

 

[jasonb75]

Given that there was no reference to the year in the output examples, I'm going to assume that it is not a required factor.
Thanks for doing the mini-sheet of the post 6 screen cap, Kirk

Book2 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	Name	January	February	March	April		Employee	Mo Name	Year	Actual Hrs	Project
2	Smith; Roger	0	1	0	0		Smith; Roger	March	2021	40	A
3	Smith; Mary	0	0	1	0		Smith; Roger	April	2021	0	A
4							Smith; Roger	May	2021	0	A
5							Smith; Roger	June	2021	0	A
6							Smith; Roger	July	2021	0	A
7							Smith; Roger	August	2021	0	A
8							Smith; Roger	September	2021	0	A
9							Smith; Roger	October	2021	0	A
10							Smith; Roger	November	2021	0	A
11							Smith; Roger	February	2021	142	B
12							Smith; Roger	March	2021	80	B
13							Smith; Roger	April	2021	0	B
14							Smith; Roger	May	2021	0	B
15							Smith; Roger	June	2021	0	B
16							Smith; Roger	July	2021	0	B
17							Smith; Mary	December	2020	10	A
18							Smith; Mary	March	2021	20	C
19							Smith; Mary	July	2021	30	C
Sheet5

Cell Formulas
Range	Formula
B2:E3	B2	=IF(MAX($A2:A2),,--(COUNTIFS($G$2:$G$19,$A2,$H$2:$H$19,B$1,$J$2:$J$19,">0")>0))