# 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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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)))))

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.

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

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

Replies
4
Views
46
Replies
7
Views
236
Replies
2
Views
115
Replies
5
Views
139
Replies
1
Views
139

### Forum statistics

1,196,506
Messages
6,015,599
Members
441,904
Latest member
edris Alsatouf ### 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