Forum Discussion
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 no member has more than 3 duties each week. Want to show number of times each name appears each week. 25 duties (rows) up to 20 members in any one week. What formula should I use?
7 Replies
Hi Steve,
It depends on how exactly you'd like your data to be organized. As variant, one set with your duties in rows, weeks as columns and member names in cells. Another set is list of members in rows, weeks as columns and number of duties per week for each member.
If above as Excel tables with names "Duties" and "Members" when the formula in first week for members will be
=COUNTIF(Duties[w1],Members[@[Member]:[Member]])
, drag it to the right on another columns.
Same for ranges could be like
=COUNTIF(B$2:B$26,$H2)
to copy it down and to the right.
Please see attached file.
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?
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.
thank you Sergei for helping. This is kind of fun figuring out this stuff. Let's suppose the following:
column B is job descriptions from B2:B37. Column C is week 1 called 1/6/2018. Entered in C2:C37 are various peoples names.
I think the formula is =countif(b$2:b$37[1/6/2018],Raymond) but its not working. Where is my error?
Maybe it should say =countif(c$2:c$37,raymond)
Hi Steve,
Yes, in this variant
=countif(c$2:c$37,"Raymond")
