# nested sumproducts and ifs

#### tripper

##### New Member
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!

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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))

Replies
1
Views
164
Replies
19
Views
527
Replies
0
Views
144
Replies
3
Views
140
Replies
1
Views
95

1,206,971
Messages
6,075,922
Members
446,170
Latest member
zzzz02

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back