Excel nerds, to me!

[Tonitrus]

Background: I run/operate my workplace/shift schedule, and have it organized on an Excel spreadsheet that I am slowly trying to improve.  However, I have one function goal that has become my White Whale...

The basic setup is that one sheet is a master personnel roster (which has all the needed date/profile of each person), and then I have separate sheets for each month.  Those monthly sheets are organized with a column of the personnel on the left side (vertically), several columns to the right of that (with data that fills in automatically from the master sheet), and then what is basically a giant square grid/table (but it's not set up as a table) that shows the days of the month.  Overall it looks something like this:


                     |                                       |       January
-------------------------------------------------------------------------------------
Name           | Headers of stuff               | 1 2 3 4 5 6 7 8 (and out to 31)
------------------------------------------------------------------------
Smith, Bubba |  stuff stuff stuff stuff stuff | W W - - W W - - W W (W is work days, - might be days off)
Flacco, Joe     |  stuff stuff stuff stuff stuff | - - W W - - W W - -


Also, I have it set up so that names can also be selected from a drop-down (using Data Validation) from the "master" sheet, which then fills in the stuff.  Alterations to the work days (leave, CTOs (compensated time off, i.e. hit days), etc) have to be hand-jammed in.  But what I want to do, is that whenever I set a specific day for a CTO, is to have a formula on the "master" sheet that will check the "Name" column for a specific name and then match up and then count in the calendar grid where "CTO" appears in the same row.  Ultimately, this formula would combine the values from every subsequent monthly sheet, and display on the "master" the number of CTO days used (the shift guys get 16 CTO days over the year for having to work holidays/12-hour days) for the entire year.

I've been scouring google/youtube, and it seems like =COUNTIF or =SUMPRODUCT might be the formulas I want to look at, but I am not finding any good examples/tutorials that match what I am trying to do.

Any ideas?

Note: Assume I am mostly an Excel idiot, as I've been teaching myself from zero knowledge over the last few weeks. 

[Liep]

So you want to count how many times CTO appears across multiple sheets for a specific name?

=countif(Sheet1!a1:Sheet1!aa1,"CTO")+countif(Sheet2!a1:Sheet2!aa1,"CTO")... would do it, no? If you keep the names on the same rows across all sheets that is.

[Liep]

Or maybe =SUMPRODUCT((Sheet1!A1:A30="Name")*(Sheet1!B1:AB30="CTO")) is easier actually...

[Tonitrus]

So you want to count how many times CTO appears across multiple sheets for a specific name?

=countif(Sheet1!a1:Sheet1!aa1,"CTO")+countif(Sheet2!a1:Sheet2!aa1,"CTO")... would do it, no? If you keep the names on the same rows across all sheets that is.

Yeah, the trick is that the row might change between sheets.

[Tonitrus]

Or maybe =SUMPRODUCT((Sheet1!A1:A30="Name")*(Sheet1!B1:AB30="CTO")) is easier actually...

A lot of what I had found last night had me looking at the SUMPRODUCT, but I haven't tried it yet.  I will today and report back. 

[Liep]

Or maybe =SUMPRODUCT((Sheet1!A1:A30="Name")*(Sheet1!B1:AB30="CTO")) is easier actually...

A lot of what I had found last night had me looking at the SUMPRODUCT, but I haven't tried it yet.  I will today and report back. 

I think that might be it. I have a similar sheet but working days are in rows. I use countifs to set multiple criteria to calculate vacation time spent, but I can't get that to work for multiple columns.

[Tonitrus]

Yeah, didn't work out.  Cannot even get it for one column.  The closest I got is a value error, but SUMPRODUCT seems to say "nope" at even using a different sheet.

[Liep]

I'm using google spreadsheet but it's usually pretty similar to excel, and it's working fine on different sheets here.

[Josquius]

Vba?

[Tonitrus]

I got it to work!

Formula ended up looking like:

=SUMPRODUCT(--(January!$B$4:$B$122="smith, joe")*(--(January!$S$4:$AZ$122)+(SUMPRODUCT(--(February....  (And it repeats from there for each month).

It seemed the "--" is what was needed to make it work.

I tried a trick I found on one sight with the idea of Defining a Name to combine the monthly sheets, in order to simplify the formula, but that didn't seem to work.  Even the above formula, while still displaying the desired number, still indicates having some kind unknown error.

The lame part is having to alter the formula for each of the 90-ish personnel on the master sheet...but that just takes copy/pasting the formula into a Word doc and doing "find/replace".

[Liep]

Can you replace "smith, joe" with "B4"? and then just pull it down?

[Tonitrus]

Can you replace "smith, joe" with "B4"? and then just pull it down?

Like I said, the names might not stay on the same row month-to-month, otherwise I would.

[Liep]

Can you replace "smith, joe" with "B4"? and then just pull it down?

Like I said, the names might not stay on the same row month-to-month, otherwise I would.

Sure, but I mean only on the mastersheet

[Tonitrus]

Can you replace "smith, joe" with "B4"? and then just pull it down?

Like I said, the names might not stay on the same row month-to-month, otherwise I would.

Sure, but I mean only on the mastersheet

Wouldn't it have to compare/match the names/CTOs on the "monthly' sheets in order to work?  Or am I overlooking something simple?

[Jaron]

Tonitrus = freaking genius