Hi Ali,
Sorry. It was my mistake. I used this number '0.0000115740695036948' which is for 1 second like time + (randbetween(5,7) * this number). I generated the date time manually like this to test the formula. Now I realise that if I work on the imported data (or just TIME function) I would have got the correct result.
Thank you for your assistance and apologize for misdirecting you.
Wow! I just noticed those values. Your statement is true. Could you educate me how it comes or how can I resolve this issue?
Hi Ali,
Sorry about screenshot issue. I've attached once again maximized. But for excel file if I change D5 value in Sheet2 from 8/10/2018 18:48:02 to 8/10/2018 18:48:01 then the problem arise.
And 362 records could be checked by 'Compare' sheet.
Please let me know if it needs to be some more elaborate.
Thank you
Hi Ali,
Sorry. Yesterday the formula result in E5 (in the attached file) showed only 2 when the date was 8/10/2018 18:17:55. Today I opened and it shows correct count.
BUT, the problem persists in the original file. I have attached new excel file with problem columns and screenshots.
Top of screenshot.jpg ensures that there is only one name = TEST.
Bottom of screenshot.jpg shows the result of countif = 361.
COUNTIF DATETIME ISSUE 2.xlsx proves how many TEST are there with the date time criteria = 362.
Now I don't know where the problem occurs.
My criteria is count if date time is between 8/10/2018 6:17:00 PM and 8/10/2018 6:17:56 PM and name matches a cell value.
Problem:
If a record has exact date time of 'Greater than or Equal to' (<=) criteria, here=8/10/2018 6:17:56, it does not calculate. It does calculate if I change date time criteria '8/10/2018 6:17:56' to '8/10/2018 6:17:57'.
Hi Carim,
Sorry for late reply. But if it would do the trick then I would be glad.
Hi Carim,
I am glad to have your help. But I know this formula since the color in the cells itself done by subroutine.
I just wanted to avoid a UDF to give other non excel skilled users of my data to filter without running UDF/Subroutine. They should be able to filter data by just using the Auto filter menu only.
Display MoreHi,
If I understand correctly your request ... you could use the standard sort from the Menu using :
1. either, using Column C, Sort by ID ... Smallest to Largest
2. or, using Column D, Sort by TintAndShade ... Smallest to Largest
HTH
Hi Carim,
Sorry about my question was not clear. I added the columns ID and TINANDSHADE only for reference. Now I've mentioned it.
Hi,
How to sort a column by it's TintAndShade property as 'Sort by A-Z'?
'Sort by Color' of Auto Filter option shows each color individually.
I want to order least value of TintAndShade to highest value.
I have attached a sample file which have colors (And ONLY for your clearance TINTANDSHADE value (of the color in third column of same row)).
Re: How to use FindNext method to find the value again that searched by Find Dialogue
Hello Admins,
I realize or remembered we could use
Range(Selection.Address).FindNext(ActiveCell).Select
to get this work. Can anyone of admins delete this thread because it is very basic?
Can anyone of admins delete this thread because it is very basic?
Hi,
I could use the below code to find the value again that searched by Find Dialogue i.e. Redo find.
Public Sub FindAgain()
Cells.FindNext(ActiveCell).Select
End SubHowever, The below code I used to find within selection does not work.
Public Sub FindAgain()
With Selection
Cells.FindNext(ActiveCell).Select
End With
End SubIs there a way or workaround for this.
It would be a great help for me.
Thank you
Re: Array Sum matching value against another sheet column and add value
NBVC,
Sorry for the late reply.
below is my UDF that I am using to get results which I want to convert to array formula where I should pass to my peers who have spreadsheet software (free version) instead MS Excel.
Public Function GETTOTALLENGTH(USERNAME As Range, FINALUSERNAMELIST As Range, COMPAREIMAGELIST As Range, FINALIMAGELIST As Range, Optional Mode = 1) As Integer
Dim FINALUSERNAMELISTDB, FINALIMAGELISTDB As Variant
FINALUSERNAMELISTDB = FINALUSERNAMELIST
FINALIMAGELISTDB = FINALIMAGELIST
Dim i, j As Integer
Dim r As Range
For i = LBound(FINALUSERNAMELISTDB) To UBound(FINALUSERNAMELISTDB)
If (FINALUSERNAMELISTDB(i, 1) = USERNAME.Value) Then
LookedUpImageRow = Application.WorksheetFunction.Match(FINALIMAGELISTDB(i, 1), COMPAREIMAGELIST, 0) + 1
If Mode = 1 Then
j = j + Worksheets(COMPAREIMAGELIST.Parent.NAME).Range("AA" & LookedUpImageRow).Value
Else
For Each r In Worksheets(COMPAREIMAGELIST.Parent.Name).Range("C" & LookedUpImageRow & ":W" & LookedUpImageRow)
If InStr(1, r.Value, " | ", vbTextCompare) > 0 Then
j = j + Len(Mid(r.Value, InStr(1, r.Value, "| ", vbTextCompare) + 2, Len(r.Value)))
End If
Next r
End If
End If
Next i
GETTOTALLENGTH = j
End FunctionRe: Array Sum matching value against another sheet column and add value
NBVC,
Sorry. It didn't give expected result.
I want to sum values by matching a name in final sheet name column then if name matches get its filename value by INDIRECT and ROW then use MATCH function to get position of filename row in Compare sheet (the filename is entered in both final and compare sheets but in different rows) and get the value in the same row of filename in Compare sheet to sum. Below is my formula which returns 1 (when I check array by F9 in this function) only instead the values those need to be added. I've also attached a sample sheet.
Readable
=SUM(IF(FINALSHEETNAMECOLUMN=NAME,INDIRECT("Compare!VALUECOLUMN"&MATCH(INDIRECT("Final!FILENAMECOLUMN"&ROW(A2:A9)),Compare!FILENAMECOLUMN,0)+1),0))
Original
=SUM(IF(Final!B2:B9=A4,INDIRECT("Compare!C"&MATCH(INDIRECT("Final!A"&ROW(A2:A9)),Compare!A2:A9,0)+1),0))Re: Excel 2007 Win7-64 Addin path reset after excel restart
Atlast a trick worked and I want to share it with you even it is not that much technical. I just removed add-in with invalid path from the add-in dialog and close the excel then start it again and added the add-in from the changed folder path. Now it got worked. I thought the Win-7 UAC had been preventing it. Thank you for all the 100 people who tried to help on this issue.
Hi,
I have Excel 2007 in Win7-64. I have placed an Add-in as in my D:\Server Backup 10.05.2015\Backup\Files Needed\Backup folder\Excel 2007 Assistance Tools\Excel Assistant.xlam and yesterday I changed the Add-in path to D:\Server Backup 10.05.2015\Excel 2007 Assistance Tools. As normal the excel 2007 said 'could not be found'. I gave 'ok' and removed the Add-in in Add-in dialog then added it from the new path. However after excel restart again I got 'could not be found' error. I tried remove and add the Add-in several times. I keep getting the error message on Excel start or restart.
Can you help me resolve this?
Thank you.
Re: COUNTIFS with MATCH as array function
XOR LX,
One last question. Can I use this formula for another sheet where names and dates are listed?
XOR LX,
It works.
Re: COUNTIFS with MATCH as array function
Wow! It is amazing. Really I am on cloud nine because It will make my head happy.
Thanks XOR LX.
Re: COUNTIFS with MATCH as array function
XOR LX,
If a student doesn't turned out on the report date then the student name shouldn't be appeared in the Names list. for the attached file only the below names should be listed i.e. 'Sheila' should not be listed since she doesn't appear on 2/19/2015 report date. (she appeared only on 2/21/2015 from 101st row).
[TABLE="width: 64"]
[TD="width: 64"]Lucas
[/TD]
Brandon
Alex
Brianna
Sagen
Cheyenne
Stefanie
Jason
Dylan
[/TABLE]
Re: COUNTIFS with MATCH as array function
PCI,
It is a good solution for me. But since I have to send this report to my head It should be automatically populate the result without ask to run pivot to my head who is not familiar with excel. So it would be great if it is a function.
I admire your valuable time.