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

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### KRice

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

#### jasonb75

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

Book2 (version 1).xlsb
ABCDEFGH
1NameJanFebMarBOG as of JanBOG as of FebBOG as of Mar
2Roger011010
3Mary111100
4Scott001001
Sheet4
Cell Formulas
RangeFormula
F1:H1F1="BOG as of "&B1
F2:H4F2=--(COUNTIF(\$B2:B2,1)=1)

#### KRice

##### Well-known Member
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
ABCDEF
8NameOnboardedJan-21Feb-21Mar-21
9RogerFeb-21010
10MaryJan-21100
11ScottMar-21001
Sheet5
Cell Formulas
RangeFormula
D9:F11D9=--(\$B9=D\$8)

##### New Member

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

Book2 (version 1).xlsb
ABCDEFGH
1NameJanFebMarBOG as of JanBOG as of FebBOG as of Mar
2Roger011010
3Mary111100
4Scott001001
Sheet4
Cell Formulas
RangeFormula
F1:H1F1="BOG as of "&B1
F2:H4F2=--(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.

##### New Member
@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:

##### New Member

@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

##### Well-known Member
Simple fix (if I've guessed correctly).
Book2 (version 1).xlsb
ABCDEFGH
1NameJanFebMarBOG as of JanBOG as of FebBOG as of Mar
2Roger011010
3Mary122100
4Scott001001
Sheet4
Cell Formulas
RangeFormula
F1:H1F1="BOG as of "&B1
F2:H4F2=--(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

##### Well-known Member
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
ABCDEFGHIJKLM
1NameOnboardedDec-20Jan-21Feb-21Mar-21EmployeeMo NameYearActual HrsProject
2Smith; RogerFeb-210010Smith; RogerMarch202140A
3Smith; MaryDec-201000Smith; RogerApril20210A
4Smith; RogerMay20210A
5Smith; RogerJune20210A
6Smith; RogerJuly20210A
7Smith; RogerAugust20210A
8Smith; RogerSeptember20210A
9Smith; RogerOctober20210A
10Smith; RogerNovember20210A
11Smith; RogerFebruary2021142B
12Smith; RogerMarch202180B
13Smith; RogerApril20210B
14Smith; RogerMay20210B
15Smith; RogerJune20210B
16Smith; RogerJuly20210B
17Smith; MaryDec202110A
18Smith; MaryDec202020C
19Smith; MaryJuly202130C
Sheet5
Cell Formulas
RangeFormula
D2:G3D2=--(\$B2=D\$1)
B2:B3B2=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

##### Well-known Member
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
ABCDEFGHIJK
1NameJanuaryFebruaryMarchAprilEmployeeMo NameYearActual HrsProject
2Smith; Roger0100Smith; RogerMarch202140A
3Smith; Mary0010Smith; RogerApril20210A
4Smith; RogerMay20210A
5Smith; RogerJune20210A
6Smith; RogerJuly20210A
7Smith; RogerAugust20210A
8Smith; RogerSeptember20210A
9Smith; RogerOctober20210A
10Smith; RogerNovember20210A
11Smith; RogerFebruary2021142B
12Smith; RogerMarch202180B
13Smith; RogerApril20210B
14Smith; RogerMay20210B
15Smith; RogerJune20210B
16Smith; RogerJuly20210B
17Smith; MaryDecember202010A
18Smith; MaryMarch202120C
19Smith; MaryJuly202130C
Sheet5
Cell Formulas
RangeFormula
B2:E3B2=IF(MAX(\$A2:A2),,--(COUNTIFS(\$G\$2:\$G\$19,\$A2,\$H\$2:\$H\$19,B\$1,\$J\$2:\$J\$19,">0")>0))

Replies
3
Views
45
Replies
8
Views
147
Replies
1
Views
98
Replies
2
Views
35
Replies
3
Views
84

1,130,211
Messages
5,640,887
Members
417,177
Latest member
njosh

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