nested sumproducts and ifs

[tripper]

Hopefully someone will be able to help me out with this! I looked through the threads but wasn't finding exactly what I was looking for.

I am creating a spreadsheet to track different employee leaves using some conditional formatting but am having trouble with the totals at the end. The conditional formatting is set up so that depending on the type of leave taken, the cell will change to the appropriate color and show the # of hrs. It would be entered in as '4.3p' or '3.2x".

My sheet is set up as follows:

Column A: Employee names (by row)
Column B3 to Column AG4:3 contains the days of the calendar
Column B4: Column AG4 contains the day of the month (numbers)

Starting in Column AH4, I have columns for each of the types of leave to calculate the total number of hours taken based on what was entered in '4.3p' or '3.2x'. That's where my problem lies. I can get the formula to work if I only have 1 conditional format used such as p (no x) but if I have both it returns #VALUE! error. I would like the specific column for the type of leave to only calculate the hrs.

Ex. 1 2 3 4 5 6 8 9 10 Total P Total X
Name 1 3.2x 3 4 5 4.3p 7 8 3.4p 10 7.7 3.2

My forumula was as follows:
=SUMPRODUCT(--(RIGHT(B4:AG4)="x"),--SUBSTITUTE(LOWER(0&B4:AG4),"x",""))

I get the error in the total columns as soon as I have 2 types of leaves entered. Make sense?

THANKS!

 

[Aladin Akyurek]

Hopefully someone will be able to help me out with this! I looked through the threads but wasn't finding exactly what I was looking for.

I am creating a spreadsheet to track different employee leaves using some conditional formatting but am having trouble with the totals at the end. The conditional formatting is set up so that depending on the type of leave taken, the cell will change to the appropriate color and show the # of hrs. It would be entered in as '4.3p' or '3.2x".

My sheet is set up as follows:

Column A: Employee names (by row)
Column B3 to Column AG4:3 contains the days of the calendar
Column B4: Column AG4 contains the day of the month (numbers)

Starting in Column AH4, I have columns for each of the types of leave to calculate the total number of hours taken based on what was entered in '4.3p' or '3.2x'. That's where my problem lies. I can get the formula to work if I only have 1 conditional format used such as p (no x) but if I have both it returns #VALUE! error. I would like the specific column for the type of leave to only calculate the hrs.

Ex. 1 2 3 4 5 6 8 9 10 Total P Total X
Name 1 3.2x 3 4 5 4.3p 7 8 3.4p 10 7.7 3.2

My forumula was as follows:
=SUMPRODUCT(--(RIGHT(B4:AG4)="x"),--SUBSTITUTE(LOWER(0&B4:AG4),"x",""))

I get the error in the total columns as soon as I have 2 types of leaves entered. Make sense?

THANKS!

Control+shift+enter, not just enter:

=SUM(IF(RIGHT(0&B2:J2,1)="p",SUBSTITUTE(B2:J2,RIGHT(B2:J2,1),"")+0,0))

=SUM(IF(RIGHT(0&B2:J2,1)="x",SUBSTITUTE(B2:J2,RIGHT(B2:J2,1),"")+0,0))