Using COUNTIF and VLOOKUP with upper and lower case letters

[mcilwrk]

I am working on a spreadsheet that counts hours worked on shifts. The
organization has been using codes like "D" for a full dayshift (8 hours) and
"d" for a short day shift (6 hours) for many years and are reluctant to
change the coding. Is there a special feature of either the COUNTIF or
VLOOKUP function that allows it to differentiate between upper and lower case
letters?. At the moment, when the functions see either a "D" or "d" it
applies the same value of hours.

Example:

Name Shift Hours
J Doe D 8
B Smith d 6
G Bush D 8
A Gore d 6
Total 28

I am using VLOOKUP to access the hours in a "table" and apply the hours
assigned to a "D" or "d" shift. I am using COUNTIF to count the "D"'s and
"d"'s in a column to see if the total shifts exceed or are less than the
budgeted shifts / hours on any particular calendar day.

I can achieve everything by changing the "d" to an "sd", but as I said, the
organization / employees have been using "D" and "d" for 20 years and are
very reluctant to change the coding sytem just to help out the scheduler.

Any help would be greatly appreciated.

 

[Don Guillett]

Look in the help index for EXACT

 

[vezerid]

I am working on a spreadsheet that counts hours worked on shifts. The
organization has been using codes like "D" for a full dayshift (8 hours) and
"d" for a short day shift (6 hours) for many years and are reluctant to
change the coding. Is there a special feature of either the COUNTIF or
VLOOKUP function that allows it to differentiate between upper and lower case
letters?. At the moment, when the functions see either a "D" or "d" it
applies the same value of hours.

Example:

Name Shift Hours
J Doe D 8
B Smith d 6
G Bush D 8
A Gore d 6
Total 28

I am using VLOOKUP to access the hours in a "table" and apply the hours
assigned to a "D" or "d" shift. I am using COUNTIF to count the "D"'s and
"d"'s in a column to see if the total shifts exceed or are less than the
budgeted shifts / hours on any particular calendar day.

I can achieve everything by changing the "d" to an "sd", but as I said, the
organization / employees have been using "D" and "d" for 20 years and are
very reluctant to change the coding sytem just to help out the scheduler.

Any help would be greatly appreciated.

To count the total number of "D" only:
=SUMPRODUCT(--EXACT(B2:B5,"D"))

To count the total hours under "D"
=SUMPRODUCT(C2:C5*EXACT(B2:B5,"D"))

In a longer table, with repetitions, to count the total hours of J Doe
(assuming your don't care about case), under "d":
=SUMPRODUCT(C2:C5*EXACT(B2:B5,"D")*(A2:A5="J Doe"))

HTH
Kostis Vezerides

 

[mcilwrk]

Thanks for the suggesion, Don.... I am a little confused about the EXACT
function and couldn't get it to work in my spreadsheet. Can you show me where
to place it in my 2 formulas below?

I use this formula to calculate total hours by looking up up the shift
identified in the "schedule" tab against the equivalent hours for that shift
in "Table" tab. (The "table" tab identifies the shifts and their hours value
eg "D" shift (8 hrs) and a "d" shift (6 hrs)

=IF(Schedule!G8="","-",VLOOKUP(Schedule!G8,Table!$A$2:$B$7,2,FALSE))

I also use this formula to determine if there have been too many shifts
assigned on any particular day. In this case there shouldn't be more than 4
"D"' shifts. Again, I need to be able to differentiate between "D" and "d"
shift letters as the maximum number of "d" shifts is 2.

=IF(COUNTIF(D836,"D")>4,"D"," ")

I apologize if this sounds confusing, its difficult to explain without
seeing the spreadsheet

Thanks again

 

[Don Guillett]

What about the other answer you got??

 

[mcilwrk]

Sorry Don, I didn't see those formulas.... I don't think they will work in my
application, but I will give it a try. The basis of the spreadsheet is a
schedule tab, an "hours" calculating tab and a shift tab that identifies the
hours that each shift represents. In the schedule tab, Column A is the name
and then there are 31 columns for each day of the month. The scheduler fills
in the shift that the person will be working each day of the month. On the
hours tab, that shift is converted to hours (using VLOOKUP) by comparing that
shift to the shifts and corresponding hours on the shift tab. All the shifts
on the schedule tab are also added up for each day using COUNTIF and at the
bottom of the schedule, it lists which shift exceeds or is less than the
budgeted number. There is a seperate line for each shift with these
exceptions at the bottom of the schedule tab.

As I said before, its difficult to try and explain what exactly is needed
without passing the spreadsheet along.

Regards,

Ken McIlwraith