Recent content by Nunes

Hi! The problem is that your "Lot No" column H is being evaluated as text and text is not counted by COUNT(). You can replace the formula in H16 by =COUNT(VALUE(H3:H15)) and enter it with Ctrl-Shift-Enter. Hope it helps Best

As for using INDIRECT, check the attached workbook. If you add an auxiliary row with the letter of the column where the information for each week is located, you can use indirect to refer to the information directly. Best Nuno

Hi Pierre Im not sure this will do the trick as I don't exactly understand what you need, but in your example file, if you put this formula in C1: ="Semaine "&TEXT(VALUE(RIGHT(B1;2))+1;"00") It will return "Semaine 02". And as you copy it to the right it will return Semaine 03, 04, 05...

Hi Shibu! As you probably understand, your nested IFs are quite impossible to understand to someone from outside of your head :) From a brief look at your code, what I could tell is that you're using a nested structure of this type: =IF($D5="A", IF('Data Input Sheet'!$F5="","...

Hi Ian Jayalaxmi's formula will return the ID number from a long string, providing it is always in the format "ID XXXXXXXX" within your string. If that's what you want/need, fine. If you want to test for the presence of a given string (that would be the 8 digit number ID) within a string (that...

Hi Giri, Your formula =SUM(SUMIFS($E$4:$E$28,$B$4:$B$28,{50861394,50861765,50861767},$C$4:$C$28,I30)) Will work fine if you just replace the hand-typed array {...} by the range reference H31:H33, like this: =SUM(SUMIFS($E$4:$E$28,$B$4:$B$28,H31:H33,$C$4:$C$28,I30)) You just have to evaluate...

Hi Barbara, These formulas are just for line 3 of your excel, you'll have to copy paste them down your list as far as you want. For the "Rate" Column (I), use the following formula: =IFERROR(VLOOKUP(E3;$O$3:$P$22;2;0);"") This will give you the ST rate for the corresponding Job Class in column...

Hi! =IF($E$2="Y",C3,"") in cell D3 and then just copy paste cell D3 downwards until the end of the list Best Nuno

Hi Barbara, It doesnt seem hard and I can try to help you but I need to know a few things before. Is column D "Shift 1 2 3" relevant for the calculation? In column E "JOB CLASS" you'll insert a job class referring to column O of the table at the right, am I correct? Columns F G and H will have...

Hi Areif, You have what I believe to be a typo in your cell F7, as it is in your example workbook. You have 55000 KM for the first service of client A when it should in reality by 5500 KM. With 5500 KM my formula returns october 9th 2014, as it should. Hope it helps Best Nuno

Hi Paul! =SUM(OFFSET(test;1;0;1;COLUMNS(test))) will sum the 2nd row of your named range "test" =SUMIF(OFFSET(test;2;0;1;COLUMNS(test));"YES";OFFSET(test;1;0;1;COLUMNS(test))) will sum the 2nd row of your named range "test" wherever the 3rd row is equal to "YES" Hope it helps Best, Nuno

Hi Hui, Are you sure Ctrl-Shift-C and Ctrl-Shift-V as a way to copy paste formats works in Excel as a default feature? Because I can't get it to work either and I've looked for it in the past and found people referring to it as a non-existing feature of excel, pointing out the apparent non...

Hi Areif If I understand correctly your problem, the formula for cell G7 should be: =E7+(H$5-F7)/((F7)/(E7-D7)) You can copy it down column G for the remaining customers. For column J, you can put this formula in cell J7: =H7+(K$5-I7)/((I7-F7)/(H7-E7)) And copy it down columns J for the...

Hi melvin! You're working exclusively with "less than" and "greater than" clauses... for each interval, you should have at one of the ends a "or equal" clause. What I mean is that your problem of wrong return when sales are zero will ocurr again, for example, if sales are 18000, or 30000, or...

If what you need is to populate column C of sheet "Sheet1" with the sum of values in column E of sheet "Data", based on the information of columns A and B of sheet "Sheet1" and on the Week selected in the yellow cell, try this in cell C5...