Posts by daddylonglegs

  • Subtract calendar months

    Re: Subtract calendar months


    You can use EDATE with negative numbers, for the requirement for the last day of the month try like this


    =EDATE(A1+(DAY(A1+1)=1),-6)-(DAY(A1+1)=1)


    Note that EDATE requires Analysis ToolPak to be enabled

  • Adding matches to INDEX MATCH

    Re: Adding matches to INDEX MATCH


    I don't believe that will work. If you need a match in all 3 columns try


    =INDEX($AC$3:$AC$16067,MATCH(1,INDEX(($B16069=$B$3:$B$16067)*($AA16069=$AA$3:$AA$16067)*($AB16069=$AB$3:$AB$16067),0),0))

  • User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT

    Re: User Defined Function (UDF) - USING WILDCARDS IN SUMPRODUCT


    You can do this with a SUMPRODUCT formula. Might not be the best approach for you but this version of your formula will treay blanks or * in the criteria range as an "any value" criteria


    =SUMPRODUCT(--(D4:D11=IF(OR(G4={"","*"}),D4:D11,G4)),--(E4:E11=IF(OR(H4={"","*"}),E4:E11,H4)),--(F4:F11=IF(OR(I4={"","*"}),F4:F11,I4)))


    Note that SUMPRODUCT lets you have up to 255 arrays in Excel 2007 (but it's limited to 30 in Excel 2003)


    COUNTIFS, btw has a limit of 127 range/criteria pairs

  • Lookup with Multiple Criteria

    Re: Lookup with Multiple Criteria


    The conditions return arrays of TRUE/FALSE values. When you divide 1 by TRUE you get 1, otherwise an error [#DIV/0!] so the lookup range returns an array of errors and 1s. Lookup value, in fact, can be any number >= 1 so it could be 1, 200 or 4.5 the formula will still work (2 is often used). When lookup value is > than any value in the lookup range it just matches with the last number, in this case the last 1 representing the last row where the 2 conditions are met. you can add more conditions easily, e.g.


    =LOOKUP(2,1/(F1=B2:B13)/(G1=A2:A13)/(H1=D2:D13),C2:C13)

  • Index/Match formula returns #N/A

    Re: Index/Match formula returns #N/A


    What formula do you have in J2? Some functions (LEFT, RIGHT, MID and others) always return text results (even if the results look numeric), this might cause the MATCH to fail. If the data is of this type (numeric) then try adding +0 to J2 to force conversion to a number, i.e.


    =INDEX('SFDC All Accounts'!$A$2:$Z$500,MATCH(J2+0,'SFDC All Accounts'!$G$2:$G$500,0),8)


    Note: this won't work for text values, if J2 is text then check that you don't have trailing or leading spaces or other characters that might make the MATCH fail


    Also I notice for that specific formula, at least, you are looking up a value in column G and returning one from H so VLOOKUP is another possibility, i.e.


    =VLOOKUP(J2+0,'SFDC All Accounts'!$G$2:$H$500,2,0)


    but, in terms of match criteria VLOOKUP works in the same way as MATCH so you'll likely have the same issues with that......

  • Schedule of hours

    Re: Schedule of hours


    If you have the days of the week in A2:A8 and the start of each shift in B2:B8, end of shift in C2:C8 then you can use this formula in D2 copied down


    =IF(COUNT(B2,C2),MOD(C2-B2,1),"")


    That will give you the total for each day, then you can sum that at the bottom.


    All cells should be formatted in required time format. Weekly sum cell should be formatted as [h]:mm to show hours over 24 if necessary

  • Schedule of hours

    Re: Schedule of hours


    What's your input data? Are you supplying a start time/date and an end time/date...or just dates? Do you want to count those hours for every day or only for certain days e.g. Monday to Friday. An example with expected result would be helpful, thanks

  • Sum Using Criteria

    Re: Sum Using Criteria


    Quote from AAE;527339

    =SUMPRODUCT(--($A$2:$A$13="E")*--($B$2:$B$13="P1"),($C$2:$C$13))



    I don't like the syntax much, you never need to use * and --

    preferable to use either

    =SUMPRODUCT(($A$2:$A$13="E")*($B$2:$B$13="P1"),$C$2:$C$13)

    or

    =SUMPRODUCT(--($A$2:$A$13="E"),--($B$2:$B$13="P1"),$C$2:$C$13)

  • Formula that returns result causes IFERROR and ISERROR to evaluate as error

    Re: Formula that returns result causes IFERROR and ISERROR to evaluate as error


    Hello Deepak,


    Frankly I'm not sure why those formulas don't work but I'll propose an alternative.....


    Your solution involves finding the specific range to evaluate using INDIRECT, OFFSET, ADDRESS etc. That has some advantages in that the range to evaluate - eventually - is small....but you lose a lot of that advantage with some of those volatile functions (and especially SUMPRODUCT) so I'd suggest this shorter formula to give the same result


    =LARGE(IF((INDEX(TestData,,1)=$B12)*(INDEX(TestData,,2)=$C12)*(INDEX(TestData,,3)=$D12)*(INDEX(TestData,,6)=$A12),INDEX(TestData,,5)),AC1)


    This needs to be confirmed with CTRL+SHIFT+ENTER


    When I wrap that in IFERROR I still get the same result


    Try that on your real data and see if it works for you........

  • Date Formating problem

    Re: Date Formating problem


    I'm not clear if this is just a problem with the years or another issue - if the result you get is 01-Jan-1900 that's just a result of zero (but formatted as a date). Are you in the UK? (do you have a default date format of dd/mm/yy?)


    If all the data has 2 digits for the day and month (with leading zeroes if necessary) then perhaps this formula will work better


    =MID(A1,FIND("-",A1)-2,8)+0


    If that doesn't work can you give a few examples where the formula(s) give the wrong results - what's the exact data, what result do you get....