Counting; Using COUNTIF etc

[dmcnab]

Hi Loungers. I need some help with COUNTIF and other 'counting' formulae. I have an assignment workbook (see sample attached). Jobs are assigned, usually, as a full day assignment. However, they can be 1/2 day jobs, or combined with another job (eg: Job 1-AM, Job 2-PM). The assignments are made on the Assignment w/sheet; these copy over onto a Weekly w/sheet that is posted online. The YTD w/sheet tallies all assignments....this is easy to do if there is only 1 assignment per day.......right now, I simply tally up all occurences (or 'mention") of each assignment but this is misleading if there are 1/2 day jobs.....eg: Hospital-AM counts as 1 'Hospital' assignment, even though it is only 1/2.....you will see examples of this on the attached workbook......the only place this doesn't happen is with vacation b/c I count VAC (and VAC-AM and VAC-PM) separately and add it all up.....but I don't want to have to make multiple columns for each assignment (in some cases there are 30 assignments, and these would make the YTD w/sheet very awkward).

Any suggestions or formulae help would be appreciated....thanks, as always.

[zeddy]

Hi

See the amended formulas in the attached file.
Works for me.

zeddy

[dmcnab]

Hi zeddy....thank you for that, but I'm not sure it captures everything that needs to be counted...I am attaching a copy of the file you uploaded with my edits...what I did was extend your formula through to all cells.....you will see, for example, that under DAVID, there are 3 assignments for "4pm - Midnite" but it only counts 1......what I having trouble with is finding a formula that will count the various types of assignments, even if there are more than 1 assignment/day.....so for DAVID, it should show 3 of the 4pm - Midnite assignments (assuming that the entry is entered in the same form..ie: not as 4pm - midnight, or, 4pm-Midnite etc etc)

[zeddy]

hi

OK.
The issue is, if some of the assignments are xxx-AM or xxx-PM, then you want to count these as half.
So in columns with headings 123, 777, 999, 908 use the 'long' formula, as this does the half day counting.

For other assignment columns like, 8am - 4pm, Midnite - 8am, you don't need to count half days for these specific types, so you use your original countif formula for these columns.

see attached.

zeddy

[dmcnab]

...???....hi zeddy.....if I assign David to "Midnite - 8am, 999" on, say, Jan 21.....it counts the Midnite-8am shift, but won't count the 999 ( as a full shift)....if I enter 999-AM or 999-PM, then it counts a 1/2 day assignment for 999....but it won't work for full day jobs.........????

[zeddy]

Blimey dmcnab!

How many hours are they doing in a day!!

OK.
So I changed the formulas again.
This time, it counts any *999* as full days (i.e. including those with 999-AM and 999-PM)
..but then it subtracts half the count for any 999-AM and subtracts half the count for any 999-PM

So now have a look at the latest version.
Does this do what you want?

I'm off to catch a train so I may check for your response later.

Regards

zeddy

[dmcnab]

Hi zeddy.....looks great....and it is much more simple than I was imagining it would be (or trying to make it...).....thank you for your help.....I will do some testing with data and get back to you but I am hopeful that it will do the trick. Thanks again...have a nice weekend.