Multiple conditions using if statement and and/or

sadonnie

New Member
Joined
Dec 17, 2018
Messages
7
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.


1617463766127.png
 

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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
How would one know when each individual was first on-boarded? That information is not shown in your table.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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)
 

sadonnie

New Member
Joined
Dec 17, 2018
Messages
7

ADVERTISEMENT

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.
 

sadonnie

New Member
Joined
Dec 17, 2018
Messages
7
@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:

1617466125400.png
 

sadonnie

New Member
Joined
Dec 17, 2018
Messages
7

ADVERTISEMENT

@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:

1617466446650.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top