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

Comments

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