Posts by DezB

  • Combine LEFT and INT into one formula

    Hi Folks


    I have a column with a date/timestamps and would like to extract the date then turn that date into something that can be used.


    I've used the LEFT function to extract the date into another cell then use the INT in another cell to convert it to a date that I want to use.


    However, I was wondering if I could combine both those functions to do the same job in one? Any ideas please??


    This is the date stamp:


    2023-05-30 16:49:16.468000+00:00


    Kind regards


    DezB

  • XLOOKUP #VALUE Error :-(

    Hi Ali.


    It's good to hear from you, thank you for responding.


    The idea is to look at the date in cell M4 and match that to row D1:I1, look at the Criteria in Cell L10 and match that to column C4:C34, also look at the Version number in cell N9 and match that row D2:I2, then return the value from D4:I34.


    In the example the result in Cell N10 should be: 565


    I hope this helps, (and makes sense...)


    Kind regards


    DezB

  • XLOOKUP #VALUE Error :-(

    Hi Folks


    I'm playing with the XLOOKUP function at the moment but have run into a problem. If I just use two nested XLOOKUP's it works fine, but as soon as I enter a third XLOOKUP I get a #VALUE error


    Can someone give me some guidance and help please?? I've attached a sample so that you can see where I'm going wrong


    Thank you.


    Kind regards


    DezB


    XLOOKUP-Error.xlsx

  • It's got to be something simple IF AND...

    Hi Folks


    Sorry if this turns out to be something really simple, just overloaded I think.


    What I'm trying to do is:


    If cell Q31 is empty, do nothing. If it has a date in it, check that date against TODAY(). If there are between 30 and 89 days return "Yes", if it's more than 89 days return "No"


    I hope someone can tell me where I'm going wrong....


    =IF($Q31="","",IF(AND(TODAY()-Q31>30<89),"Yes","No"))


    Thank you..


    Kind regards

    DezB

  • Countfs with ELSE I think

    Hi Rory.


    That's where I also have a quandary as it needs to count is a 1. Basically what's being recorded is if there is a change in the position of funding. I was trying to start with greater than 0 but I guess what it really needs to look at is, is there a figure anything other than 0.00. I hope this helps, it's been a long week, although it's been a short week in the UK :)

    Kind regards


    DezB

  • Countfs with ELSE I think

    Hi All.


    I'm trying to get a COUNTIFS to work where it looks at a date and an ID number then counts data in 3 columns. However, what it needs to do is look at the first column, if it is 0.00 then ignore it, if the next column is 0.00, ignore that too but if there is a number greater that 0.00 in the last column, count it as 1


    If there are figures are greater than 0.00 in one or more of the columns then also count it as 1 and total up all the 1's in the formula column.


    I've attached a sample, I really hope that helps.


    Any assistance will be gratefully received, thank you......


    Kind regards


    DezB


    Countifs.xlsx

  • What is wrong with this formula??

    Hi Folks.


    I know this is going to be a silly error on my my part, but can anyone tell me why excel is not seeing this as a formula??


    =IFERROR(SUMIFS('Errors-YTD'!$H:$H,>0,'Errors-YTD'!$L:$L,"<="&D$14,'Errors-YTD'!$C:$C="Customer",'Errors-YTD'!$E:$E,$B33),0)


    H:H is a column of numbers and I need it to sum up all the numbers that are above 0


    L:L is a column of dates and the formula looks for any dates less that or equal to the date in D14


    E:E is a list of codes and the formula looks for the code that is in B33


    Somehow I'm missing a quotation or a comma I think..


    Any help, much appreciated as usual please... :)


    Kind regards


    DezB

  • Formula summing up even if the reference cell is empty

    Hi Carim


    Thank you for your quick reply, you really do come to my aid. The reason for the "*" is that The name in K24 is contained within a string the column C:C


    For example K24 contains the word Eastbourne and in C:C there could be multiple entries that contain that word, such as

    "1234 Eastbourne abcd" and

    "1234 Eastbourne CDEF" etc etc


    Currently it looks for that word Eastbourne then sums up whatever is in J:J


    I really hope I'm making sense here??? :)


    Kind regards


    DezB

  • Formula summing up even if the reference cell is empty

    Hi Folks


    I have this formula which looks at a cell, then looks for every instance of the name in that cell on another sheet and sums the figures in another column wherever that name occurs. However, if the reference cell is blank, for some reason it then sums everything in the column that contains figures. I don't understand why???


    =SUMIF('Data_Sheet-YTD'!$C:$C,"*"&'Analysis-YTD'!$K24&"*",'Data_Sheet-YTD'!$J:$J)


    Data_Sheet-YTD C:C contains all the names the name in Analysis-YTD K24 is looking for and Data_Sheet-YTD J:J contains the figures to be summed.


    I've tried using and IFERROR addition, no joy...


    Any thoughts please??


    Kind regards and many thanks..


    DezB