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
 

sadonnie

New Member
Joined
Dec 17, 2018
Messages
7
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
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,589
Office Version
  1. 365
Platform
  1. Windows
Looks good, Kirk (y)

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
Joined
Dec 9, 2003
Messages
1,132
Office Version
  1. 2019
Platform
  1. Windows
Good point...thanks, Jason.
 

Watch MrExcel Video

Forum statistics

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