concatenate and if statements

Hi Sergei.  I want to say how helpful you have been in my project.  Really appreciate it.  Working on SchHist now.  Want to transcribe each month's entries into the appropriate rows without affecting the previous month's entries.  Thought I could use an if function based on the date in LeadSch!L$2 then just add 30 days for subsequent months.  Used this formula

=if(LeadSch!$L$2=1/6/2018,LeadSch!D$5)

should return Steve, actually returns FALSE.  I assume I've got the syntax wrong, but I don't see it.

have modified formula to be

=if(LeadSch!$l$2=datevalue("1/6/2018")),LeadSch!D11)

but when I change the datevalue for subsequent months the value in earlier month shows FALSE and of course I want to preserve the earlier values.

Steve

Hi Steve,

I'm not sure with logic of your rules

"Same task last 4 weeks" - but you have only 4 weeks at all, what to compare?

"More than 4 tasks" - that's better use conditional formatting. Add rule to the cell D63

=(D63>4)

and apply it to entire names/weeks range (see attached)

"Sound vs usher" - if simply compare names when

=IF(OR(D$12=$D43,D$12=$D44,D$43=$D44),"conflict", "okay")

"Worship vs Ministry teams" - was in previous post

"Kids class vs Torah Portion" - again names?

=IF(D$35=$D28,"conflict", "okay")

"Worship vs Ushering" - compare team to name, but two times

=IF(
   COUNTIF(OFFSET(WorshipTeam!$A$2,1,MATCH(D$21,WorshipTeam!$A$2:$E$2,0)-1,30),D$43) +
   COUNTIF(OFFSET(WorshipTeam!$A$2,1,MATCH(D$21,WorshipTeam!$A$2:$E$2,0)-1,30),D$44),
"conflict","okay")

"Teaching vs Ushering" - as above

=IF(OR(D$25=$D43,D$25=$D44,D$43=$D44),"conflict", "okay")

"More than 1 Liturgy" - I guess we shall compare if no matches in D17,D18 and D19,D20. When

=IF(SUMPRODUCT(--(D$17:D$18=D$19:D$20)),"conflict", "okay")

and in attached

Sergei, I'm ready to throw in the towel.  When you provide answers to my formula questions the formulas are hard for me to understand. everything you provide works but I understand so little I can't expand them to apply to other formula needs.  I have 7 formulas to figure out (see attached file) and am running up to a deadline.  If not the actual formulas (hopefully) please provide some guidance.

Steve

Hi Steve,

The formula could be like

=IF(COUNTIF(
  OFFSET(MinistryTeam!$A$2,1,MATCH(D$42,MinistryTeam!$A$2:$E$2,0)-1,30),
  D$45),"conflict","okay")

You have to select list of people (MinistryTeam) the name of which is given in cell D$42 ("Raymond"), after that you count how many times the name in D$45 is in this list and return result depends on no matches or more than one match.

Similar is for other formulas, first step you have to define the list of people in the selected team. Again, with Tables will be easier, but we use OFFSET here.

For example, for another conflict check if someone from selected Worship team is in selected MinistryTeam the formula could be

=IF(SUMPRODUCT(COUNTIF(<WorshipTeam>,<MinistryTeam>)), "conflict", "okay")

or with how we define the teams

=IF(SUMPRODUCT(COUNTIF(
  OFFSET(MinistryTeam!$A$2,1,MATCH(D$42,MinistryTeam!$A$2:$E$2,0)-1,30),
  OFFSET(WorshipTeam!$A$2,1,MATCH(D$21,WorshipTeam!$A$2:$E$2,0)-1,30)
)),"conflict","okay")

Please see attached

Hi Steve,

Okay, will check bit later

Thank you so much for your help in the struggle to do the leadership counting.  Everything works across the whole month.  Now on to the next task.  I've attached the updated file for you to look at.  At the bottom of the second page of LdrSch you'll see a section called conflict analysis which looks for a situation in which a person might have been scheduled to do two tasks at the same time.  In the row called "afterglow" vs Ministry" I've put this formula

=if(d$45=(match(d$42,MinistryTeam!$a$2:$e$2,0)),"conflict",0)

which should return "conflict" because Mike B. is both on afterglow and on the ministry team of Raymond.  Instead it returns 0.  Can you tell me where I went wrong?

Hi Steve,

In such case the formula could be

= COUNTIF(D$2:D$45,$C65) +
 COUNTIF(OFFSET(WorshipTeam!$A$2,1,MATCH(D$21,WorshipTeam!$A$2:$E$2,0)-1,30),$C65) +
 COUNTIF(OFFSET(MinistryTeam!$A$2,1,MATCH(D$42,MinistryTeam!$A$2:$E$2,0)-1,30),$C65) -
 (($C65=D$21)+($C65=D$42))

For first COUNTIF

WorshipTeam!$A$2 is the cell with most left column name;

MATCH finds which column is for the team name in D21;

OFFSET returns the values (30, last parameter) in the column shifted from A2 on one row (second parameter) down and on MATCH()-1 to the right.

and you count how many times value in C65 is in above range.

Please see attached.

Sergei, I don't think I have created the formula properly.  In your earlier message you refer to (MinistryTeam[[#data],[" & d$42 & "]]"),$c65),0)

I think that means "go to Ministryteam worksheet, look at those cells and at cell d$42, add them up, and return the number or 0 if they don't match". 

But which cells?  One column eg A2:A11? or all the cells eg A2:E11?

Also, what is the purpose of the 2nd ampersand and space in [" & d$42 & "]?

Hi Steve,

I guess that's since COUNTIF on WorMinTms doesn't work, it shall return error and IFERROR converts it to 0.

Better to use tables and structured references, it could take some time to become more or less familiar with them, but saves a lot of time in future when you modify your data.

However, if you prefer ranges that will be another syntax for COUNTIF. Better if you share your updated file to be more concrete.

have created a worksheet called WorMinTms (Worship and Ministry Teams) where columns a-e are worship teams and columns g-k are ministry teams.

First attempt at formula is the following

=countif(d$2:d$45,$c65) + iferror(countif(indirect("WorMinTms[[$a2:$a11],

[" & d$21 & "]]"),$c65),0) + iferror(countif(indirect("WorMinTms[[$j2:$j11],

[" & d$42 & "]]"),$c65),0) - (($c65=d$21)+($c65=$d42))

This produces a count of 2 for Raymond.  It should be 1 each for D9, D19, D42, WorMinTms J2 - 1. Resulting in a count of 3.

That's "nice to have", just in case. If for any reason you substitute into the formula wrong column name, COUNTIF will return the error (perhaps #REF!, not sure), IFERROR converts the result into 0 for such cases.

Which, in general, is correct - no one person is in the team which doesn't exist.

I get your point re the duplication of names. probably came about out of my confusion. As I implement your suggestions that will go away probably.  One question though:  In your sample formulas, what does iferror do?

Steve,

Your formulas itself are not very complex, it's practically impossible to support them. Same information is repeated in several places, any change could require correction of formulas,...

Not sure I understood all your logic, as a first step I'd suggest to create two Excel tables: WorshipTeams and MinistryTeams (at any place you'd like, in attached file in Sheet1). It easy to expand adding new columns and change just by renaming headers. Your data validation drop down list in D21 will be

=INDIRECT("WorshipTeams[#Headers]")

and for D42

=INDIRECT("MinistryTeams[#Headers]")

(copy and paste to another week columns).

Formula to calculate number of duties (first person for first week, i.e. in D62) will be

=COUNTIF(D$2:D$45,$C62) +
   IFERROR(COUNTIF(INDIRECT("WorshipTeams[[#Data],[" & D$21 & "]]"),$C62),0) +
   IFERROR(COUNTIF(INDIRECT("MinistryTeams[[#Data],[" & D$42 & "]]"),$C62),0) -
   (($C62=D$21)+($C62=D$42))

First part counts how many times the name is repeated in column D,

second and third checks if it is in WorshipTeam / MinistryTeam defined in D21 and D42,

fourth part deduct the result on one if name of the above team is the same as the name of the person.

Copy the formula on next weeks for same person and when drag down till end of persons list. Same formula for everyone, no need to change it if you make any changes in above tables. So, that's one time job, after that only copy/pasting.

As a comment, I didn't catch why it shall be #3 for Raymond in week 1 -  he is in cells D9, D19 and within the team defined in D42. Totally 3.

As another comment didn't catch why do you enter exactly same names two times, in column C of first sheet and in column A of WkrTable sheet. At least you may convert the latest in one more table and use it for drop-down list selecting names in LeadSch sheet.

Could be other improvements in data structure, but again, not sure I understood all logic.