Countifs with multiple criteria in single criteria range

Dear All,

I have worksheet in which I am calculating Days Worked and Over Time.

I have single criteria range with multiple criteria e.g. criteria "P" has value 1, criteria "PP" has value PP*2,
criteria "P1" has value P1*0.125 then how to define it?

PP = 8 hrs regular duty + 8 hrs OT = 16 hrs
P = 8 hrs regular duty
P1 = 8 hrs regular duty + 1 hrs OT = 9 hrs
P2 = 8 hrs regular duty + 2 hrs OT = 10 hrs and so on......

I used the formula =SUM(COUNTIF(F8:AJ8,{"PP","P","P1","P2","P3","P4","P5","P6","P7"})), but it is not giving me the result what I want.
Means how to define "PP"*2, P1*0.125 ........?

Please help.

Thanx in advance.

Suhas

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Using a table below in A2:C10:

Code Regular OT
P 8 0
P1 8 1
P2 8 2
P3 8 3
P4 8 4
P5 8 5
P6 8 6
P7 8 7
PP 8 8

this gives TOTAL hours based on codes in F8:AJ8

=SUMPRODUCT((F8:AJ8=$A$2:$A$10)*(($B$2:$B$10)+($C$2:$C$10)))

You could also combine regular hours and OT in column B so you have 8,9,10 etc

=SUMPRODUCT((F8:AJ8=$A$2:$A$10)*($B$2:$B$10))

Is this what you require??

Hi John Sir,
Thanx very much for reply.

I am attaching my excel file in which I described what I want.

File Name - Duty+OT Calculation.xlsx

Please give the solution.

Suhas

Bebo Sir,

Formula result is 296 hrs. means 31 Days regular Duty & 6 Days Over Time. So my AK5 Cell should display 31 days i.e. 248 hrs., Cell AL5 should display 6 days i.e. 48 hrs. and Cell AM5 should display actual hours (applicable when Over Time is less than 8 hrs i.e. 1 day). File attached herewith.

Bebo Sir,

Formula result is 296 hrs. means 31 Days regular Duty & 6 Days Over Time.
So my AK5 Cell should display 31 days i.e. 248 hrs., Cell AL5 should display 6 days i.e. 48 hrs. and
Cell AM5 should display actual hours (applicable when Over Time is less than 8 hrs i.e. 1 day). File attached herewith.

Formulae are same as given previously.

AL5= Total OT HOURS

AM5= Total hours including OT

Modified AM

=IF($AK5<30,0,SUMPRODUCT(($F5:$AJ5=$AR$2:$AR$10)*($AS$2:$AS$10)))

gives REGULAR hours

Or cell AM5:

=SUMPRODUCT(--MID(SUBSTITUTE(F5:AJ5&0,"P",8),{1;2},1))

Hi John Sir,

Thanx for reply, seen the attached file, but that is not what I deserved.

What I want is as below -

AK is no. of days i.e. 31 or 30 (month-wise) - means in this case AK5 should display 31 (days)

AL is no. of days i.e. If an employee worked more than total 248 hours (31 days * 8), it will be calculated as OT - means in this case AL5 should display 6 (days) (48 hrs / 8 hrs)

AND AM is no. of hours i.e. If an employee worked more than total 248 hours i.e. 31 days and he worked extra 2 hours only then AK5 should display 31 (days), AL5 should display 0 (days) and AM5 should display 2 (hrs.). If worked extra 10 hrs., then AK5 should display 31 (days), AL5 should display 1 day (i.e. 8 hrs.) and AM5 should display 2 hrs.


Thanking you.

Suhas

@nflsales: Good point - incomplete specification by the OP!