Hello Dileep,
In Summary!B5
=SUMIF(INDIRECT(TEXT($A5,"'dd-mm-'")&"!C:C"),B$3,INDIRECT(TEXT($A5,"'dd-mm-'")&"!F:F"))
Then copy down & other Amount columns.
Haseeb
On Mon, Jul 21, 2014 at 2:42 PM, Dileep Kumar wrote:
> Dear Friends,
>
> Please find attached Daily Reporting Formate of
Hello Atul, try this
Assume B2:B11 is %, in C2 then copy down.
=SUMPRODUCT((ISNUMBER(MATCH(ROW(INDEX(A:A,1):INDEX(A:A,RANK(B2,B$2:B$11))),INDEX(RANK(B$2:B$11,B$2:B$11),0),0))+0))
___
HTH, Haseeb
On Wednesday, March 21, 2012 12:24:21 AM UTC+3, Atul wrote:
>
>
> Hi,
>
> I need help
Hi,
Assume the sheet name always be in dd-mmm-yy format. I have define a name
CurrPeriod, you need to define it on your file. Just change Start & End
Date reference in this formula. I also change some Locations for SALES in
multiple sheets to make sure it works :-)
See the attached.
_
Hello Sundarvelan,
Assume your sheet name always be in dd-mmm-yy format, if so see the
attached.
In Summary B1 & B2, enter the Start & End Date, So this will calculate
results between these sheets.
___
HTH, Haseeb
--
FORUM RULES (986+ members already BANNED for violation)
1) Use con
If you have space in the sheet name will require an apostrophe before 7
after the sheet name. When ever you use INDIRECT use the apostrophe. excel
will ignore, If doesn't require it. So use like this,
*=SUM(INDIRECT("'"&A6&"'!G:G"))*
___
HTH, Haseeb
--
FORUM RULES (986+ members alread
Hi Chandru,
In a cell eg: A1 use this formula to count total count, where E1 contains
in Data!E3:E640
=SUMPRODUCT(ISNUMBER(SEARCH(E1,Data!E3:E640))*(E1<>""))
Then in A3 with CTRL+SHIFT+ENTER, rather than just ENTER
=IF(ROWS(A$3:A3)<=A$1,INDEX(Data!E:E,SMALL(IF(ISNUMBER(SEARCH(E$1,Data!E$3:E$64
Hello Arpita,
A little confusion in the part "=><1/-1"
If you want to SUM all >0 values, you can use
*
=SUMIF(A1:A16,">0",A1:A16)*
If you are looking to SUM between >=-1 to 1 (minus 1 to positive 1), use
*
=SUM(SUMIF(A1:A16,{">=**-**1",">1"},A1:A16)*{1,-1})*
HTH
Haseeb
On Monday, February 27,
Hi Sam,
I think this part ISNUMBER(--MID(A3,6,4)) will allow entry with 3 spaces
before the fourth digit :-) , like
ABCDE 1D
Hi Vijayendra,
This is my try. Firstly define 3 names to avoid INDIRECT function.
Name: *Alpha*
On refers to:
={"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"
Hi Lee, try
For the Week,
=WEEKNUM(TEXT(A1,"\-00\-00"))
For the month,
=TEXT(TEXT(A1,"\-00\-00"),"")
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 Pr
Hello Faisal & Evey one,
This is what my understanding,
*If any code repeats after a dest, just before this code consider as final
dest.*
Eg:
Row 101.
DMS-BAH-AUH-CGK-AUH-DMM
AUH repeats after CGK, so final dest, CGK.
Row 109,
DMS-BAH-DAM-BAH-DMM
BAH repeat after DAM, so final dest, DAM
Hello Amar,
As you said colors are changing through CF, check is the conditions TRUE or
FALSE, then you can assign the values, if it is true. If you can share a
dummy file or your CF condition to the group, it would be helpful.
___
HTH, Haseeb
--
FORUM RULES (934+ members already BANN
Try this *Array Formula*,
=INDEX(MID(A6,ROW(A$1:A$50)*4-3,3),MAX(IFERROR(MATCH(MID(A6,ROW(A$1:A$50)*4-3,3),IF(MID(A6,ROW(A$1:A$50)*4-3,3)="","A",MID(A6,ROW(A$1:A$50)*4-3,3)),0),"")))
Copy down..
___
HTH, Haseeb
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise,
Hello Vikash,
Assume the values are in A2:J2. In A3, copy across.
=INDEX($A$2:$J$2,RANDBETWEEN(1,COUNTA($A$2:$J$2)))
This may give you duplicate values. if you only want the unique values. use
these.
A3,
=INDEX($A$2:$J$2,RANDBETWEEN(1,COUNTA($A$2:$J$2)))
B3, *Array Formula must hit CTRL+SHIF
Hello Ankit,
Another way. See the attached.
___
HTH, Haseeb
--
FORUM RULES (934+ 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 at
Assume Bill ID's are numeric. Use this *ARRAY FORMULA *(must hit *
CTRL+SHIFT+ENTER*, rather than just ENTER). then copy down.
=SUM(SIGN(FREQUENCY(IF(Customer=E2,Billid),Billid)))
If you want to count the total unique ID's regardless of customers, use
this with just ENTER.
=SUM(SIGN(FREQUENCY(B
Or,
Use a validation list of all priorities in A1 & just select the priorities
from the list. If you are using list, instead of LOOKUP you can link them
directly as =$A$1, for the numbers as =B$1
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles
Also, select col_E press Ctrl+H to activate Find/Replace,
from the keyboard NUMERIC side (right side of the keyboard, not the top
row) Press Alt+0160 in Find What box: and click Replace All. Now you can do
a simple SUM.
Or use this Array Formula,
=SUM(IFERROR(--SUBSTITUTE(E2:E18,CHAR(160),""),
Hello Asa R,
Assume your numbers are in A:A, in B1 enter *Unique_Numbers*, B2 copy
down...
=SMALL(A:A,1+COUNTIF(A:A,"<="&MAX(B1,*-*9E300)))
The above will give the unique numbers in Ascending Order,
=LARGE(A:A,1+COUNTIF(A:A,">="&MIN(B1,9E300)))
This above will give the numbers in Descending O
You can also use SMALL function like,
=SMALL(A:A,FREQUENCY(A:A,MAX(A:A)-1))
will give the 2nd largest number, even if you have duplicate or not.
___
HTH, Haseeb
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like
Hi,
Try also without any tables,
=LOOKUP(A2,DATE(YEAR(TODAY()),-{1342,35,12,9,6,3}+MONTH(TODAY()),0),{"Prospect";">
12 Month";"9 - 12 Month";"6 - 9 Month";"3 - 6 Month";"0 - 3 month"})
Or, if you activate Analysis ToolPak AddIn (unless if you are using XL2007
or later), you can simplify,
=LO
Let me explain my best. I hope this help.
9E300;
When you use it in Excel it will changed to 9E+300. It is a scientific
notation of a big number. 300 zeros after 9, so it is like
9000…… This will give a big number. As you know LOOKUP will
always look for the number <=Lookup_Val
Hello Santosh,
Another one.
=IFERROR(LOOKUP(9E300,SEARCH({"Sa","Su","Sha"},B4),{"Hero","Boy","Shaan"}),"")
__
HTH
Haseeb
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula
Hello JP, Please do not post multiple thread for the same query.
In Summary B4, copy down & across.
=LOOKUP(9E+300,INDIRECT("'"&$A4&"'!B1:"&ADDRESS(2^20,COLUMNS($A4:B4
__
HTH
Haseeb
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread title
Hello Noorie & Sam;
Good ones, but as you know, If a site has LARGE date more than 1 (*not sure
does the OP have this situation or not*), this wouldn't work as
expected. I think the attached would work.
__
HTH
Haseeb
--
FORUM RULES (934+ members already BANNED for violation)
1)
Hello JP,
In Summary B4, copy down & across.
=LOOKUP(9E+300,INDIRECT("'"&$A4&"'!B1:"&ADDRESS(2^20,COLUMNS($A4:B4
__
HTH
Haseeb
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need
Hello Smitha;
If you need a formula use,
=REPLACE(A1,1,FIND(":",A1),"")
Or
Select your data, press CTRL+H;
Find What: **:*
Click Replace All
Hope this helps
Haseeb
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like P
Or, another one;
=INT((WEEKDAY(B1-DAY(B1)+1-6)+EOMONTH(B1,0)-(B1-DAY(B1)+1))/7)
Even if B1 is not the first day of the month, it will work. If you are
using XL2003 or prior versions, EOMONTH function require Analysis ToolPak
Addins to be installed & activated.
__
HTH
Haseeb
--
FORUM
Akhilesh,
Please attach a dummy sheet with your desired result. So the members can
look on it.
__
Haseeb
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code P
Hello Sunil;
Also, you can use SUMIF,
=SUMIF(A2:A11,"<="&TODAY(),B2:B11)
_
HTH
Haseeb
--
FORUM RULES (925+ 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 Ad
Mahreen,
If you have multiple ratio values define a name for them each, like Table1,
Table2 & choose which table to use.
See the attached.
HTH
Haseeb
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Pleas
Congratulations Sam
--
FORUM RULES (925+ 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) Don't post a
Hello Sam;
A nice solution with Validation.
Hello Mahreen;
If you use "3", "2", "1" (number inside "") this will return the number as
text. When you do calculation excel is considering these as text. Use it
without "" or you can simplify the formula to;
=IFERROR(LOOKUP($E$2,{-9E+300,3;3,1;4,0
For the actual text, rather than SUBSTITUTE, one way is, C7 & copy down.
=IF(ISNA("remark
"&LOOKUP(2,MATCH("*"&TEXT(ROW(CurrPeriod),"mm/dd/")&"*",A7,0),ROW(CurrPeriod))),"",A7)
Haseeb
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor
Hello Siti;
Simple & a nice formula.
--
FORUM RULES (925+ 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.
Hello Lee,
See the attached. If you are using XL 2003 or prior WEEKNUM function need
Analysis ToolPak Addin to be installed & activated. Other formulas will
work with all versions.
Hope this help you.
Haseeb
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurat
Hello Hanna;
Another one.
=SUMPRODUCT(LOOKUP(DAY(EOMONTH(ROW(INDIRECT(B3&":"&C3)),0)),{28,29,30,31},A3/{28,29,30,31}))
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, C
Akhilesh,
=ISNA(MATCH($A5,Sheet1!$A:$A,0))
If A5 is *NOT* contains in Sheet1Col_A this will return TRUE, if contains
FALSE. So Advanced Filter will look for each cell in the range & extract
the record which is = TRUE. see the below link. Hope this help you more.
http://www.contextures.com/xlad
Hello XLS S;
Let me try to explain my best :)
In sheet2 B1, used a formula to get all items in sheet1, which is
=COUNTA(Source!A3:Z65536)
B2, used too get total columns used, which is
=COUNTA(Source!A2:Z2)
Based on the attached, there are 3 columns & 300 rows. So, first 3 rows
need the data
Also,
Another one to avoid OFFSET volatile, In E2 & copy down.
=MAX(B2:INDEX(2:2,MATCH("z",$1:$1)-1))
HTH
Haseeb
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem
Hello Ken;
Also, =MATCH(9E300,$B:$B) will give the last row number contains number. so
you can define name range with this as length
See the attached. Same data as Sam posted.
HTH
Haseeb
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor t
Hello Ken;
Also, =MATCH(9E300,A:A) will give the last row number contains number. so
you can define name range with this as length
See the attached. Same data as Sam posted.
HTH
Haseeb
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor th
Hello Akhiles,
Use Advanced Filter.
In Sheet2, insert 3 blank rows above the heading, then use this formula in
A2
=ISNA(MATCH($A5,Sheet1!$A:$A,0))
Then select Sheet2 data, use Advanced Filter.
In Advanced Filter,
Select Copy to another location
List Range: Select Sheet2 data.
Criteria Range
Hello Zeunasc,
You can also do it through Find/Replace command.
Select the column, press Ctrl+H to bring Find/Replace command.
Find What : *(hitcnt**
Replace All with blank.
Again;
Find What : *0x**
Replace All with blank.
Note: There is a * after each Find item.
HTH
Haseeb
--
--
Hello Zeunasc;
I am not good in to explain something. Here is an attempt. Hope this would
help you.
A2 = Start Date, here 10/20/2011
A4 = End Date, here 10/25/2011
Basically 10/20/2011 is 40836 (40836 days after 1/1/1900) &
10/25/2011 is 40841 (40841 days after 1/1/1900). If you change the
Hello Zeunasc;
Try the attached.
Enter a start date in A2, & End date in A4.
HTH
Haseeb
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldaily
Hello Arslan;
If you have real dates & time, doesn't need MOD function.
A1 = some date & time; eg: 4/11/2011 08:39:20 PM
Use in B1 for date, format as date
=INT(A1)
Use in C1 for time; format as time
=A1-B1
HTH
Haseeb
--
-
Hello Nemi;
You can also use simple HLOOKUP.
=HLOOKUP(C2,I$3:T$105,MATCH(A2,H$3:H$105,0)+B2,0)
C2, here column heading & B2 is the row values located in Col_H
HTH
Haseeb
--
--
Some important links for excel users:
Hello Surya;
If your data is sorted by Country in Ascending order, use the file
sorted_by_Country. The other one will work with sorted or unsorted data.
Also used dynamic range.
HTH
Haseeb
--
--
Some important li
Hello Sharma;
As Paul said; use a helper column & use formulas in the attached.
HTH
Haseeb
For the help & tips, visit;
http://www.excelfox.com/forum/forum.php
--
--
Some important links for excel users:
1. Follow u
Hello Anil,
A short version to Ms.Exl.Learner
=TRIM(MID(SUBSTITUTE(A2,"/",REPT(" ",255)),4*255,255))
HTH
Haseeb
For help & tips visit;
http://www.excelfox.com/forum/forum.php
--
--
Some important links for excel u
Hello Sharma,
Assume your dates are in Col_A. Try this in C3;
=LARGE(A:A,1+COUNTIF(A:A,">"&B2))
HTH
Haseeb
For free MS Product help, visit;
http://www.excelfox.com/forum/forum.php
--
--
Some important links for ex
Another one;
=INT((WEEKDAY(A1-*1*)+B1-A1)/7)
Note: There are 2 opening brackets before WEEKDAY.
A1 = Start_Date
B1 = End_Date
*1* = Sun, *2* = Mon, *3* = Tue, *4* = Wed, *5* = Thu, *6* = Fri, *7* = Sat
If you want to count multiple days between 2 dates use then inside an Array
separated by co
Hi, Karan,
Not a good one, but try this Array Formula,
If you are using XL 2003 or prior,
=MAX(IF(ISNUMBER(MID(SUBSTITUTE(A2,"
",""),ROW(INDIRECT("1:"&LEN(A2))),{6,7})+0),MID(SUBSTITUTE(A2,"
",""),ROW(INDIRECT("1:"&LEN(A2))),{6,7})+0))
If you are on XL 2007 or later,
=MAX(IFERROR(MID(SUBSTIT
Hi John,
Try these,
K3, copy down.
=LOOKUP(LOOKUP(9E+300,C3:J3),N3:X3,N$2:X$2)
L3, copy down.
=LOOKUP(LOOKUP(9E+300,C3:J3),Z3:AJ3,Z$2:AJ$2)
This will look the last entered value.
HTH
Haseeb
--
--
Some important
Hi Sundarvelan,
Since you are dealing with valid numbers use SMALL function.
in G3 enter,
=MIN(B:D)
G4, copy down.
=IFERROR(SMALL(B:D,COUNTIF(B:D,"<="&G3)+1),"")
HTH
Haseeb
For free help visit;
http://www.excelfox.com/forum/
--
--
Hello Prabhu,
Also try this;
Sub DelZeroRows()
Dim LR As Long
Dim Length As Long
Application.ScreenUpdating = False
LR = Range("P" & Rows.Count).End(xlUp).Row
Count = Application.WorksheetFunction.CountIf(Range("P2:P" & LR), 0)
For Length = LR To 2 Step -1 '<-
Hello Prabhu,
Also try this;
Sub DelZeroRows()
Dim LR As Long
Dim Length As Long
Application.ScreenUpdating = False
LR = Range("P" & Rows.Count).End(xlUp).Row
Count = Application.WorksheetFunction.CountIf(Range("P2:P" & LR), 0)
For Length = LR To 2 Step -1 '<
Hello Naresh,
Use SUMIF with SUMPRODUCT,
=SUMPRODUCT(SUMIF(A:A,H2:H4,B:B))
HTH
Haseeb
For fre help visit;
http://www.excelfox.com/forum/forum.php
--
--
Some important links for excel users:
1. Follow us on TWI
Hello Naresh,
See the attached. You can Add/Delete New/Old entries.
HTH
Haseeb
For free help, visit,
http://www.excelfox.com/forum/forum.php
--
--
Some important links for excel users:
1. Follow us on TWITTER fo
Hello Lee,
See the attached with Array Formula.
HTH
Haseeb
For free MS product help, visit;
http://www.excelfox.com/forum/forum.php
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tr
Congratulations Rajan...!
Regards, Haseeb
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.l
Hello dewbro,
See the attached. Used DMIN to avoid Array Formulas. Insert 3 rows above the
heading & use as shown in the attached.
HTH
Haseeb
For tips visit;
http://www.excelfox.com/forum/forum.php
--
--
Some impo
Hello Chandru,
See the attached. If doesn't have 12 entries it will adjust to available
entries. If do have more than 12 week entries will show the last 12 week
data.
HTH
Haseeb
For tips visit;
http://www.excelfox.com/forum/
--
Hello Maulik,
If you just have single characters in cells like "A","P" etc... use this,
AF2 copy down.
=LOOKUP(10,SEARCH("p",B2:AE2),B$1:AE$1)
Or,
=LOOKUP(2,1/(B2:AE2="p"),B$1:AE$1)
The first one is more faster than second one.
HTH
Haseeb
For tips visit;
http://www.excelfox.com/forum/forum.
Hello Anil.
You can use VLOOKUP for Reverse Lookup. Consider A1:B20 is the data. E2 is
the Lookup_Value from Col_B. So use this,
=VLOOKUP(E2,CHOOSE({1,2},$B$2:$B$20,$A$2:$A$20),2,0)
CHOOSE({1,2},$B$2:$B$20,$A$2:$A$20) will give the array of Col_B value first
then Col_A. So VLOOKUP will work as
Hello Kalyan,
If you just have two customers, you can hard coded as array & use 1 SUMIFS
with SUM.
Without checking MOC in B3
=SUM(SUMIFS('Raw data'!D:D,'Raw data'!A:A,$B5,'Raw
data'!C:C,{"Distributor","Super stockist"}))
With MOC in B3,
=SUM(SUMIFS('Raw data'!D:D,'Raw data'!A:A,$B5,'Raw dat
Hello Sundar,
Also, a non array formula with just ENTER.
=SMALL(A:A,COUNTIF(A:A,"<=0")+1)
HTH
Haseeb
For tips visit;
http://www.excelfox.com/forum
--
--
Some important links for excel users:
1. Follow us on TWITTE
Assume you need the entries, Last in First, First in Last. Let's say the
entries are in A2:A10.
Try this in B2, copy down.
=INDEX(A$2:A$10,ROWS(A2:A$10))
HTH
Haseeb
For Tips visit;
http://www.excelfox.com/forum/forum.php
--
Hello Anil,
Also, If you have other text or charracters other than #n/a and just need to
count the numbers only, use this,
=COUNT(INDEX(MID(C2,ROW(INDEX(C:C,1):INDEX(C:C,LEN(C2))),1)+0,0))
Copy down
HTH
Haseeb
For tips visit;
http://www.excelfox.com/forum/forum.php
--
-
Hello Vijay,
See the attached.
HTH
Haseeb
For tips visit;
http://www.excelfox.com/forum/forum.php
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.c
Hello Venkat,
Use a separate sheet to get the unique offices. You can hide this sheet, if
you don't want to show. Also use dynamic range name, so it will update
automatically when add/delete entries.
Assume there are no blank cells in Data Col_A Offices. If there is blank
change the formula in
Hello Vickey,
Use DMAX formula. See the attached.
HTH
Haseeb
>
>
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedI
Hello Prabhu,
Use this in B2 & copy down.
=IF(ISNUMBER(SEARCH("Opt. ",A2)),LOOKUP(2,1/ISERROR(SEARCH("Opt.
",A$2:A2)),A$2:A2)&SUBSTITUTE(A2,"Opt. ","#"),A2)
Then copy Col_B *Paste Values *in Col_A. Then after delete Col_B.
HTH
Haseeb
--
--
Thank you Dave for the last cell point & the correction. I didn't notice the
last column being Yes.
I think Column U don't have to be blank, because we have an additional
column (A) without Yes or No, so If we change the formula without including
column U, like,
=SUMPRODUCT(--(B2:T2="Yes"),--(
Hello Prabhu,
Use SUBSTITUTE. or excel's Find/Replace command
SUBSTITUTE is a case sensitive function, so you need convert the cell to
UPPER or LOWER, so use,
=SUBSTITUTE(UPPER(A1),"ABC","")
Or,
=SUBSTITUTE(LOWER(A1),"abc","")
All the letters in 1st output will be in CAPITAL LETTERS
All the
Hello Azeema,
Use this formula, say in V2 & copy down.
=COUNTIFS(A2:S2,"Yes",B2:T2,"<>Yes") This will only work XL2007 or later.
If you want to work with XL 2003 or prior use SUMPRODUCT
=SUMPRODUCT(--(A2:S2="Yes"),--(B2:T2<>"Yes"))
See the attached
HTH
Haseeb
--
--
Hello Nilesh,
Another way, K2 copy down.
=TEXT(I2,"\/00\/00")-TEXT(J2,"\/00\/00")
If you don't want to see -days use
=ABS(TEXT(I2,"\/00\/00")-TEXT(J2,"\/00\/00"))
Or,
convert the Dates to actual date, for doing this,
Select the whole column I:I, go to,
Data >> Text to
A2, copy down as many needed
=IF(ROWS(A$2:A2)<=A$1,ROWS(A$2:A2),"")
Or, If you are inserting rows between A2 & the last rows this will not work
as expected, so use this one;
=IF(COUNT(A$1:A1)<=A$1,COUNT(A$1:A1),"")
copy down as many needed
HTH
Haseeb
--
-
Hi,
C2 copy down.
=IFERROR(INDEX(D$1:SF$1,MATCH(TRUE,INDEX(ISNUMBER(1/D2:SF2),),0)),"Not
Started")
HTH
Haseeb
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
ht
Hello AIren,
Consider B1:B6 we have these values;
B1=1
B2=1
B3=Blank
B4=A
B5=Blank
B6=Blank
=SUMPRODUCT((B1:B6<>"")/COUNTIF(B1:B6,B1:B6&""))
Firstly take (B1:B6<>"")
This will check B2:B30 is blank or not, If it is blank will give FALSE, if
not will give TRUE. So will get like this
{TR
Hello,
If you want to count the unique numbres in a range, use this with just
Enter, *doesn't require* Control+Shift+Enter.
=SUM(SIGN(FREQUENCY(A:A,A:A)))
HTH
Haseeb
--
--
Some important links for excel users:
Hello Susan,
If you just have 3 sheets, Sheet1, Sheet2 & Sheet3. use this;
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&{1,2,3}&"'!D:D"),"C
Wagner",INDIRECT("'Sheet"&{1,2,3}&"'!E:E")))
Or,
Enter all sheet names in a range, say in J2:J3, then use this;
=SUMPRODUCT(SUMIF(INDIRECT("'"&$J$2:$J$4&"'!D:D"),
82 matches
Mail list logo
