Forum Discussion

Steve Haines's avatar
Steve Haines
Copper Contributor
Jan 03, 2018

concatenate and if statements

have if statement for a current project: =Ij($d$42="Raymond",countif($b$57:$n$57,WkrTable($d$20),0) wich can have far too many variants (up to 50).  Far too cumbersome, far too complex.  Have tri...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 12, 2018

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

Steve Haines's avatar
Steve Haines
Copper Contributor
Jan 15, 2018

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

  • Steve Haines's avatar
    Steve Haines
    Copper Contributor
    Jan 21, 2018

    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

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 15, 2018

    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

Resources