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 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...
Hi,
You should probably elaborate a bit on what exactly you need, because your post is not that specific and the attached file doesn't help that much either.
Best,
Nuno
Hi,
I solved it with the same method NARAYANK991 did, but with a little twist, cuz his methos will return N/A because some of your data are N/A's that will bust the sumproduct formula if not handled
I just introduced na IFERROR(,0) in each parameter of the sumproduct and evaluated the formula...
hey @Jonathan G !
for the dynamic named range you can use Chihiro's proposed solution.
i'm used to use a slightly different solution that ill explain
For example, in the workbook i've sent you with your problem solved, if you go to the Name Manager, the named range cbrandslist is defined as...
The catch with my solution as it is right now is that if you want to add or remove elements from the Clothes List or from the Watch List, you'll be stuck with the original named ranges, but you can also solve that by dynamically naming a range.
If you want help with that drop a line here, ill...
Here's a file with an implementation of the explanation I provided.
In yellow there are data validated cells that dynamically change accordingly to the header of G2.
the auxiliary table i mention is in the sheet Aux.
Best,
Nuno
You can solve that by using named ranges.
Name the clothes brands range list clotheslist, for example.
Name the watch brands range list watchlist, for example.
Create an auxiliary table somewhere in you workbook with the possible headers for your data on the first column and the named ranges...
hi there!
there's nothing wrong with your sumproduct.
the problema lies in the N/A's in cells CX4 and CY4 of the "LMPA" sheet.
you have 2 ways to solve this:
either you replace those N/A's by some number (zeros i guess?) or you can replace the:
LMPA!$C$4:$DF$4
part of your sumproduct by...