>
> Hello Chandru,
Fiirst of all, I am not sure where is these conditions in the sample, you
provided. (a1:a5>=1)*(a1:a5<=4). I only can see two columns in the sample.
ColA: 10;20;30;40;50. ColB 100;200;300;400;500.
Anyway, you can use ISNA(MATCH or COUNTIF to check LIST 2 is NOT contains
i
Hello Ankit,
Attached is a one way. This is not a best one, some one will have better
options.
I have added two columns. One is Dpmt% & 2nd one is No: of Dpmt, which
is return through a formula.
No:of Dpmt column, MUST NOT be empty or zero. Better to use dynamic range
or convert your range to
Hello Matty,
FYI;
The above formula required Product entered in Sheet2, must be grouped in
Sheet1. Otherwise wouldn't give accurate answers. Here is a Non-Volatile &
Non-Array formula,
In B6,
=SUMPRODUCT((TEXT(Sheet1!$A$3:$A$122,"mmm;;")=TEXT($A6&1,"mmm;;"))*(LEFT(
Sheet1!$B$2:$Q$2,LEN(B$4))=
One common way to extract *n*th word. Assuming word separator is 'space'
=TRIM(MID(SUBSTITUTE(" "&TRIM(A1)," ",REPT(" ",250)),n*250,250))
Eg: If we want to extract 3rd word, use it like;
=TRIM(MID(SUBSTITUTE(" "&TRIM(A1)," ",REPT(" ",250)),3*250,250))
If a cell doesn't have specified nth
Hello Karan,
In C2, with CTRL+SHIFT+ENTER, and copy down.
=IFERROR(INDEX(A$2:A$100,MATCH(1,IF(A$2:A$100<>"",IF(ISNA(MATCH(A$2:A$100,C$1:C1,0)),1)),0)),"")
If you need Unique Names in *Ascending Order*,
In C2, with CTRL+SHIFT+ENTER, and copy down.
=IFERROR(INDEX(A$2:A$100,MATCH(0,IF(A$2:A$100<>
Hello Amit,
Try this in G15 and copy down & across.
=SUMPRODUCT(ISNUMBER(MATCH($F15&"|"&$F$5:$F$9,$A$5:$A$21&"|"&$B$5:$B$21,0))+0,G$5:G$9)
Regards,
Haseeb
--
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Hel
Hello Hilary,
If you want to return the exact 2nd occurrance value, you can use VLOOKUP
like this,
=VLOOKUP(A14,INDEX(A:A,MATCH(A14,A:A,0)+1):INDEX(B:B,65536),2,0)
Regards,
Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor threa
Hello Satish,
One way is VLOOKUP
E5, then copy down & across.
=IFERROR(VLOOKUP(E$4,$M$4:$S$10,MATCH($D5,$M$3:$S$3,0),0),"")
Regards,
Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Ne
Hello Deba,
FYI:
If you use "LD000" as constant this will add with each number, means when
gets 10-99, this will become, LD00010, when gets 100-999 will become
LD000100.
If you want to keep the format, "LD", 1-9 - LD0001, 10-99 - LD0010,
100-999 - LD0100, 1000- - LD1000, here is on
Hello Amit,
Keep PO Bill & Party Bill on different tab, so can easily update it. See
the attached.
Addition: If you use Table format, formula will automatically insert, when
you enter a new data. Or use dynamic range to limit calculation range. If
you wish, you can also create a validation l
!A:A"),A1,INDIRECT("'"&$D$1:$D$10&"'!B:B")))
Regards,
Haseeb A
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and
Hello Shekhar,
If the values always be numbers, here is one way.
=LOOKUP(9E300,INDEX(Sheet2!$1:$65536,MATCH(A2,Sheet2!$A:$A,0),0))
Where A2 = name_in_Sheet1
Regards,
Haseeb A
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread
*Happy Birthday Ayush*
Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Do
Hello Lee,
This should work with just ENTER.
in AC2, then drag down.
=SUMPRODUCT((A$2:A$23=AB2)*(B$2:Z$23<>"")/COUNTIF(B$2:Z$23,B$2:Z$23&""))
Adjust the ranges. See the attached.
Regards,
Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread title
Hello Divaker,
Consider data is in A1:Z1.
If you are looking to find first #N/A in the range & count #N/A after that
occur consecutively, here is a one way:
Only work with XL2007 or later:
=IFERROR(COUNTIF(OFFSET(A1,,MATCH(TRUE,INDEX(ISNA(A1:Z1),0),0)-1,,MATCH(1,INDEX(1-ISNA(OFFSET(A1,,MATCH(T
Hello Aamir,
Assume always will have 'TOTAL' in the bottom of each group, If so you can
use VLOOKUP.
See the attached. For the sorting is an Array Formula, so must be with CSE.
I have added some dummy data for various groups. Use dynamic range, if you
have more data.
___
HTH, Haseeb
Nikhil, One way:
Assume date is in A2. So
In B2 for the Curr Quarter.
=TEXT(LOOKUP(MONTH(A2),{1,4,7,10})*29,"")
Then in C2
=TEXT((1&B2)+99,"")
Copy across to E2.
See the attached.
___
HTH, Haseeb
nikhil wrote:
>
> Now answer is right ,
>
> i.e. Date 01/05/2010 ( DD/MM/
Nikhil,
I got answer July instead of April. There were two formulas in my last
reply. First one for Current Quarter, 2nd one for Next Quarter.
See the attached.
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thr
Hello Nikhil,
This will give the current quarter.
=TEXT(LOOKUP(MONTH(A1),{1,4,7,10})*29,"")
This will give the Next Quarter
=TEXT(LOOKUP(MONTH(A1)+3,{1,4,7,10,13})*29,"")
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thre
You can get this with just one SUMPRODUCT,
=SUMPRODUCT((B$2:B2=B2)*(A$2:A2={"P","S"}),C$2:C2*{1,-1})
Which is on the file in the last reply.
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Plea
Congratulation Don. Very happy to see your name on this thread.
Regard,
Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
Hello Mothilal,
See the attached. if you are on XL 2007 or later use SUMIFS
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Ne
Hello Kenrock,
Assume you will have header in row 1 & data starts on A2 onwards. So the
below formula will sort the data alphabetically without duplicates.
In B2 with CTRL+SHIFT+ENTER, rather than just ENTER. then copy down as
necessary.
If you are on XL2007 or later,
=IFERROR(INDEX(A$2:A$100
Amit,
If you are looking to count unique Vehicles in a date & a period. here is
one way:
For a single date:
=SUM(IF(FREQUENCY(IF(TEXT(H$4:H$9,"md")=TEXT(CabOutDate,"md"),IF(C$4:C$9<>"",MATCH(C$4:C$9,C$4:C$9,0))),ROW(C$4:C$9)-ROW(C$4)+1),1))
Change cabOutDate to your reference where is
Hello Amit,
This will count the unique numbers in A:A
=SUM(SIGN(FREQUENCY(A:A,A:A)))
with just ENTER.
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula P
Hello Lee,
One way:
=SUM(SUMIF(G3:G409,{">350",">449"},$D$3:$D$409)*{1,-1})
Will sum 351-449. if you want to include 450, change 449 to 450
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Plea
Hello Asa,
Thank you for your explanation.
Deba, also hope this website also help you.
http://www.excelfunctions.net/ExcelCellReferences.html
If that works for you, you can avoid LEFT function,
=INDEX(C$6:C$47,MAX(IFERROR(MATCH("*"&MID(TRIM(D6),2,{1,2,3,4,5,6,7,8,9})&"*",SUBSTITUTE(C$6:C$47,"
Hello Shahzad,
Also If you doesn't have always 6 digit this will extract the available
numbers (<=6 digits) If any,
=IFERROR(LOOKUP(99,--RIGHT(C2,{1,2,3,4,5,6})),"")
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles.
Good One Maries :)
As you know OFFSET is volatile. A non volatile formula,
=IFERROR(INDEX($B$4:$E$4,MATCH(B$9,INDEX($B$5:$E$7,MATCH($A10,$A$5:$A$7,0),0),0)),"")
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thre
Hello Arslan,
One way;
=SUMPRODUCT(SUBTOTAL(109,OFFSET(AB11,ROW(AB11:AB249)-ROW(AB11),)),--(AB11:AB249<0))
If you have more condition just add it.
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, li
Hello Deba,
Try this in E6 with *CTRL+SHIFT+ENTER*, rather than just ENTER
=INDEX(C$6:C$47,MAX(IFERROR(MATCH("*"&LEFT(MID(TRIM(D6),2,10),{1,2,3,4,5,6,7,8,9,10})&"*",SUBSTITUTE(C$6:C$47,"
",""),0),"")))
Then drag down
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for vi
FYI,
If you have blank cells or zero in a date column & corresponding column has
numbers MONTH function will sum those numbers if the Month in A1 is 1.
because in excel blank & zeros consider as Jan (in month case) To avoid
this this need to use a another condition like (Sheet1!A2:A200<>"") or
Hi,
In a an adjustent cell try to multiply with 24 & round by 3
=ROUND(A1*24,3)
Change A1 to TOI cell.
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula
Hello Shrini,
=COUNTIF(D:D,">="&StartDate)-COUNTIF(D:D,">="&EndDate+1)
Or,
=SUMPRODUCT(COUNTIF(D:D,">="&IF({1,0},StartDate,EndDate+1))*{1,-1})
this way able to use whole column reference in SUMPRODUCT
Change StartDate & EndDate to cell reference
___
HTH, Haseeb
--
FORUM RULES (986+
Hello Pawel,
Another one.
=TEXT(A1*IF(ISNUMBER(FIND(".",A1)),100,1),"00\:00")
If you want to enter 2 for 2'O clock enter as 200 or cell format as text
then enter like 2.00
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titl
Hello Sreejith,
As Seema said, not sure about how do you want to compare due to duplicate
trade ID's. If you are looking to total the headings based on TradeID's use
SUMIF.
In C11, then copy down. Assume Trade Id always be in column A in detailed
sheet
*
=IFERROR(SUMIF(Detailed!A:A,C$8,INDEX(
Hello Kiran,
In G4 with CTRL+SHIFT+ENTER, rather than just ENTER
=IFERROR(INDEX(D$2:D$498,MATCH(1,IF(ISNA(MATCH(D$2:D$498,G$3:G3,0)),IF($C$2:$C$498="Agent",1)),0)),"")
Then copy across & down. if you have huge list, this will slow down the
sheet performance.
___
HTH, Haseeb
--
FORUM
This is a duplicate thread. There are couple of replies to your last
thread. Check out that please.
___
HTH, Haseeb
On Thursday, March 22, 2012 8:22:08 AM UTC+3, nike wrote:
>
> Please find the attachment of my criteria.
>
>
> Please refer column "A" and i want result in Column "'B" wh
Hello, try also,
=TRIM(SUBSTITUTE(MID(A2,FIND(" ",TRIM(A2)&"
"),LEN(A2)),TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99)),""))
On Wednesday, March 21, 2012 12:00:09 PM UTC+3, nike wrote:
>
> Hi Team,
>
> How to remove a words left text and right text of reference
>
>
> Please refer attachm
39 matches
Mail list logo
