# Multiple conditions using if statement and and/or

##### New Member
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.

##### New Member
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!

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### KRice

##### Well-known Member
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
ABCDE
5NameDec-20Jan-21Feb-21Mar-21
6Smith; Roger0010
7Smith; Mary1000
Sheet5
Cell Formulas
RangeFormula
B6:E7B6=--(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

##### Well-known Member
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.
Excel 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

##### Well-known Member
Good point...thanks, Jason.

Replies
3
Views
41
Replies
8
Views
130
Replies
1
Views
88
Replies
2
Views
27
Replies
3
Views
76

1,129,262
Messages
5,635,140
Members
416,843
Latest member
mrbrown91b

### 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.

### Which adblocker are you using?

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

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