What equation will total number of cells with specific words?
 

I have set up a spread sheet where each cell represents an employee covering
a half hr shift during a certain time of day and want it to sum up how many
cells contain each person's name. Therefore if their name is listed 80 times
that week I know we have them scheduled to cover 40 hrs (or whatever they
need that week).

Can anyone tell me how I can set up an equation to automatically sum/total
the number of times that a specific word/name is found in a cell or just in
the spread sheet in general? It would be most helpful!

What equation will total number of cells with specific words?
 

There are several ways. I am a fan of SUMPRODUCT.

If your schedule contains the names in B2:B81, use
=SUMPRODUCT(--(B2:B81="name")) where "name" is the person's name in quotes or
a reference to the person's name without quotes.

"Tidejwe" wrote:

I have set up a spread sheet where each cell represents an employee covering
a half hr shift during a certain time of day and want it to sum up how many
cells contain each person's name. Therefore if their name is listed 80 times
that week I know we have them scheduled to cover 40 hrs (or whatever they
need that week).

Can anyone tell me how I can set up an equation to automatically sum/total
the number of times that a specific word/name is found in a cell or just in
the spread sheet in general? It would be most helpful!

What equation will total number of cells with specific words?
 

Assume the names are in the range A1:A100.

=COUNTIF(A1:A100,"Joe")

Or, use a cell to hold the name:

C1 = Joe

=COUNTIF(A1:A100,C1)

--
Biff
Microsoft Excel MVP


"Tidejwe" wrote in message
...
I have set up a spread sheet where each cell represents an employee
covering
a half hr shift during a certain time of day and want it to sum up how
many
cells contain each person's name. Therefore if their name is listed 80
times
that week I know we have them scheduled to cover 40 hrs (or whatever they
need that week).

Can anyone tell me how I can set up an equation to automatically sum/total
the number of times that a specific word/name is found in a cell or just
in
the spread sheet in general? It would be most helpful!

What equation will total number of cells with specific words?
 

That's excellent! It worked Great! I later tried to export it to Google
Docs' Spreadsheet to share with others to be able to check from home, and
apparently Google has disabled this feature or something. :( People online
mentioned it used to work for them and then suddenly stopped. Sadness! Oh
well, at least it works in Excel. Great answer! It was EXACTLY what I was
looking for. If you know how to get it to also work in Google Doc's Spread
Sheet, then that would be doubly awesome.

"sb1920alk" wrote:

There are several ways. I am a fan of SUMPRODUCT.

If your schedule contains the names in B2:B81, use
=SUMPRODUCT(--(B2:B81="name")) where "name" is the person's name in quotes or
a reference to the person's name without quotes.

"Tidejwe" wrote:

I have set up a spread sheet where each cell represents an employee covering
a half hr shift during a certain time of day and want it to sum up how many
cells contain each person's name. Therefore if their name is listed 80 times
that week I know we have them scheduled to cover 40 hrs (or whatever they
need that week).

Can anyone tell me how I can set up an equation to automatically sum/total
the number of times that a specific word/name is found in a cell or just in
the spread sheet in general? It would be most helpful!