Sum function with If, Mid and Left doesn't work

asked 2016-01-21 14:20:53 +0200

Paul Meems gravatar image

A xlsx-file was send to me, made with Ms-Office. In it I need to add my hours appending a letter: F for free, H for Holiday. It will calculate my time I spend throughout the year on free time, study time, sickness, etc.

My first 31 columns are the days of the month. Then I have several additional columns for the time I was free, sick etc. Every row is a month.

These additional columns are calculated using this formula. This one is for the free time, appended with F: =SUM(IF(C8:AG8<>"";IF(MID(C8:AG8;2;1)="F";--LEFT(C8:AG8;1)))) This formula fails, but works in MS-Office. I don't think the exact above formula works in MS-Office, but this is the formule LibreOffice shows, it looks like it has been converted in some way. I don't have access to MS-Office.

I've been trying to fix the formula myself but didn't succeed. What the formula should do is look at the range C8:AG8, which are the days of the month. If the cell has a value, check if it ends with F if so get the number before F and sum it. The cell value can be empty or 8F or 8S.

Any help will be much appreciated.

edit retag flag offensive close merge delete


Please try this variant =SUMPRODUCT(IF(C8:AG8<>"";IF(RIGHT(C8:AG8;1)="F";VALUE(LEFT(C8:AG8;LEN(C8:AG8)-1));0);0)) Is it correct?

JohnSUN gravatar imageJohnSUN ( 2016-01-26 13:52:20 +0200 )edit