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.

 

[sadonnie]

Thank you both @jasonb75 and @KRice for the solutions. This =--(COUNTIF($B2:B2,">0")=1 fixed my problem.

However, I will try your approaches on the master data in order to save time and skip the pivot table all together. Will post an update once I give it a try.

Thanks!

 

[KRice]

Jason did something interesting that avoids the helper "onboarded" column in my earlier post. Borrowing from that idea, this is a slightly modified version that also goes directly from master list to final format:

MrExcel20210401.xlsx
	A	B	C	D	E
5	Name	Dec-20	Jan-21	Feb-21	Mar-21
6	Smith; Roger	0	0	1	0
7	Smith; Mary	1	0	0	0
Sheet5

Cell Formulas
Range	Formula
B6:E7	B6	=--(AGGREGATE(15,6,DATEVALUE($J$2:$J$19&" 1, "&$K$2:$K$19)/(($L$2:$L$19<>0)*($I$2:$I$19=$A6)),1)=D$1)

 

[jasonb75]

Looks good, Kirk

One point to note, it will only work with US (or similar) regional settings, i.e. those using the MDY format. Anytone using DMY settings would need to make a small change to the formula.

=--(AGGREGATE(15,6,DATEVALUE("1 "&$J$2:$J$19&$K$2:$K$19)/(($L$2:$L$19<>0)*($I$2:$I$19=$A6)),1)=D$1)

 

[KRice]

Good point...thanks, Jason.