# Adding LEFT to SUMPRODUCT OFFSET formula in a scrolling calendar

#### Nanaia

##### Active Member
I am trying to modify an existing working formula to have it SUMIF (I think) the the 1st character in the cell meets the criteria. This existing formula searches the entire row in a scrolling calendar and counts the number of cells that have the letter located in cell NO\$5 (which is "V"). This is used for counting the number of occurrences a vacation day is used. My employer is breaking vacation time down to be available by the hour instead of having to take a full or half day. They now want to track the number of hours as well as the number of days those hours were taken. So if people take 2 hours vacation on Monday and 4 hours of vacation on Friday they have used 6 hours of vacation on 2 days. The calendar spans from column B to column NI, in case that information is needed.

The formula below would be my base formula. All it counts is the number of days - but not if there is a number used after the letter code. What I'd like for them to be able to do is use the code for the vacation (V), paid time off (P), or sick (S) (the letter code is what is in cell NO5), and enter the number of hours was used. For example, if we use "V2" it would be two hours of vacation used.

I thought I could use LEFT to identify the type of time used by looking at the first character in the cell and also to have Excel total the numerical value next to the letter. I am not familiar with the OFFSET function though so I am unsure how to modify the formula to count the days. I don't know how to add a SUMIF function to this formula. I figured (hoped?) once I figured out how to use SUMIF in this formula then I could tweak it creating a new formula in a new cell to calculate the accumulated hours.

Excel Formula:
``=SUMPRODUCT((OFFSET(\$A8,0,1,1,372)<>"")*(OFFSET(\$A\$3,0,1,1,372))*((OFFSET(\$A8,0,1,1,372))=NO\$5))``

• Khoff_2005

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### JGordon11

##### Active Member
If the V always has a number after it you can use the first formula. If there can be just a V then need to assign a default hours to it as in the second formula where I used 8.

aFilterR1.xlsm
ABCDEFGHIJ
838V5PV6SV7V8V12
938V5PV6SV7VV12
Sheet 8
Cell Formulas
RangeFormula
A8A8=LET(r,B8:NI8,SUM(IF(LEFT(r,1)="V",--RIGHT(r,LEN(r)-1))))
A9A9=LET(r,B9:NI9,SUM(IF(r="V",8,IF(LEFT(r,1)="V",--RIGHT(r,LEN(r)-1)))))

#### Nanaia

##### Active Member
If the V always has a number after it you can use the first formula. If there can be just a V then need to assign a default hours to it as in the second formula where I used 8.

aFilterR1.xlsm
ABCDEFGHIJ
838V5PV6SV7V8V12
938V5PV6SV7VV12
Sheet 8
Cell Formulas
RangeFormula
A8A8=LET(r,B8:NI8,SUM(IF(LEFT(r,1)="V",--RIGHT(r,LEN(r)-1))))
A9A9=LET(r,B9:NI9,SUM(IF(r="V",8,IF(LEFT(r,1)="V",--RIGHT(r,LEN(r)-1)))))
@JGordon11 Thank you. There will always be a numerical value after the letter code because they want to indicate the code and the number of hours used. Your formula works for totaling the number of hours. How would I count the number of occurrences? I also need to count how many days, not the hours from each day, just the number of days there was an occurance. COUNTIF gives me a #SPILL! error.

#### Nanaia

##### Active Member
I think I got it...
Excel Formula:
``=SUMPRODUCT(LEN(B8:NI8)-LEN(SUBSTITUTE(B8:NI8,"L","")))``

#### JGordon11

##### Active Member

=SUM(--(LEFT(B8:NI8,1)="V"))

Replies
7
Views
94
Replies
1
Views
43
Replies
3
Views
245
Replies
1
Views
38
Replies
10
Views
158

### Forum statistics

1,147,518
Messages
5,741,637
Members
423,675
Latest member
Dea21 ### 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.

### Which adblocker are you using?    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

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