Forum Discussion

Steve Haines's avatar
Steve Haines
Copper Contributor
Dec 28, 2017

formulas and functions

I am creating a spreadsheet describing responsibilities of members of my church for each Sabbath meeting of the month.  Consequently 4 or 5 columns each month. Want to double check my entries so that...
Formulas and Functions
Steve Haines's avatar
Steve Haines
Copper Contributor
Dec 29, 2017

Hi again Sergei.  have encountered an additional situation.  some jobs in the LdrSch worksheet require two individuals which I have entered eg as "Fraziers" which tells me "Martina" and Raymond" or as "Eldon/Brian".  Excel doesn't recognize this info.  You'll recall I'm using countif to find total number of entries for any specific name.  Using =countif(c$2:c$37, "Raymond").  How can I expand formula to include the other info.  Also space requirement have forced some info in a column and some in a row, so i'll need to combine two countif statements. How to proceed?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 30, 2017

Hi Steve,

 

If expand like

=countif(c$2:c$37, "*" & "Steve" & "*" )

it returns wrong result since calculates Steve, Steven, Stevenson as one person - they all have Steve within the name.

First, to calculate few names within one field you have to define boundaries for each name. That could be, for example, the space which separates the names within one cell. When we have to take into account extra spaces could appear, that's usual story. We may generate the formula which handles that, but perhaps better to use comma as separator, that's more natural and easier to calculate. The formula could be like

=SUMPRODUCT(--ISNUMBER(SEARCH($H2 & ",",B$2:B$26 & ",")))

which virtually adds commas at the end of each string and search now names like "Steve," calculating their sum. If the name is within the text with few names like "Steve, Rob" it also will be calculated.

Please see attached file.

 

If your names are structured differently please provide small sample file with how it looks like.

  • Steve Haines's avatar
    Steve Haines
    Copper Contributor
    Dec 31, 2017

                           A                  B                   C               D

    9               Greeters                                             Raymond

    10                                                                         Martina

     

    18            Blessing Liturgy                              Raymond/Martina

     

    34             Ministry Team                                     Raymond

     

    44                                                         Ministry Teams

     

    48              Raymond         Martina         (Plus others...)

     

    Using the formula =sumproduct(--isnumber(search($A48 & "/",D$2:D$37 & "/"))) should return 4 but returns 2.  What have I done wrong?

     

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

      Hi Steve,

       

      The formula correctly returns 3 as it shall be (please see attached), other words how many times text in cell A48 (Raymond) appears in column D.

       

      But I fully lost with how your data is structured.

Resources