multiple IF and COUNTIF statements

**Kwarren** · 12-22-2009, 01:21 PM

Here is my problem (this is part of a timesheet): Cell B2 is custom formatted as [h]:mm with a formula of =((E2)/24).
If cell A2 contains a number, cell B2 divides that number to give me hours. Therefore, if A2 contains the number 8, B2 will look like “8:00.”

Now for the problem part: my workplace always allows us to leave 2 hours early the day before a holiday break. What I would like to happen is if cell A2 contains an “h” (for holiday), I want the “h” to be counted as a 2 hour time period and appear as “2:00” in cell B2. If cell A2 contains a number, I want it to use the formula mentioned above.
The number that appears in cell B2 is calculated elsewhere in the spreadsheet to add up to an 8 hour day.
Using the formula: =COUNTIF(E2,"h")/24*2 gives the correct results if A2 contains an “h” but I don’t know how to use both formulas together.

Thanks for your help,
Kirk

**Domski** · 12-22-2009, 01:47 PM

If you are putting an h into A2 where are you expecting the employee to enter the hours that they work? Would you be expecting them to enter H6 in the cell signifying a holiday and that they worked six hours resulting in a total of 8 hours from the formula?

If so it would be easier to have another cell as the holiday inicator.

Dom

**Kwarren** · 12-22-2009, 02:06 PM

I did consider adding an E/O column (for early out). Perhaps this would be less confusing for all using the spreadsheet.

**Kwarren** · 12-22-2009, 03:00 PM

Dom,

The spreadsheet contains columns for Time In, Time Out, Sick Day, Vacation Day, Personal Day, and Comp Time. I wanted to be able to place an “h” in the Comp Time column to add up to an 8 hour day. So, if someone worked from 8am to 2pm and place an “H” in the Comp Time column, they would have their 8 hours. (The “H” could be used without burning actual comp time)

**kchocho** · 12-29-2009, 02:43 PM

I'm sorry to add to this another question, but I thought it may be related. I have a set of data and I'm not sure if it can be handled with this multiple IF or COUNTIF statement.

I have data with numerous columns of identifiers, and hundreds of data each. Right now two columns of interest with one representing location and the other age. I have it sorted by location. I need the equation to COUNT my data based on these two conditions but don't know how to do it since it also need to take into consideration WILDCARD text in the location column everywhere in my spreadsheet for these two columns.

(Note that locations are written 5A-52-01, or 7A-72-02)

I want to count location anytime "5A*" is noted AND if age is greater than or equal to 50.

How can I get these two?

Thank you.