Search:

Type: Posts; User: Marcelo Branco; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.54 seconds.

  1. Replies
    1
    Views
    45

    Re: Conflict with

    See if this example helps



    A
    B
    C
    D
    E
    F
  2. Replies
    3
    Views
    82

    Re: Averageifs on Duplicate Columns

    You are welcome. Thanks for the feedback.

    M.
  3. Replies
    3
    Views
    82

    Re: Averageifs on Duplicate Columns

    Welcome to Mr Excel forum

    Try
    =AVERAGEIFS(INDEX($F$5:$EU$37,MATCH("Mutual Fund Name",$A5:$A37,0),0),$F$4:$EU$4,"Peer Group Percentile")

    Hope this helps

    M.
  4. Replies
    5
    Views
    162

    Re: Sumif with VBA

    If you want to add numbers with decimal places, 100.17, 50.45 etc, you need a Double variable to store the result.

    See...
  5. Re: Distinct Result from Multiple Countif

    oops... the code above has a typo

    Should be


    Select Case vData(i, 1)
    Case 238, 397 'IDs of interest
    If vData(i, 4) = sCat Then dic(vData(i, 3)) = Empty
    End Select
  6. Re: Distinct Result from Multiple Countif

    New version - much faster, took only 0.5688 seconds to do the job!



    Sub xTest()
    Dim sCat As String
    Dim dic As Object, vData As Variant, i As Long
    Dim StartTime As Double,...
  7. Re: Distinct Result from Multiple Countif

    Fazza,

    A macro dealing with 550K rows took 6.0221 seconds to do the job.



    Sub aTest()
    Dim arrId As Variant, sCat As String
    Dim dic As Object, vData As Variant, i As Long
    Dim...
  8. Re: Distinct Result from Multiple Countif

    Fazza,
    I strongly agree, but as OP said...



    So, I'm just trying to meet what he asked, ie include a new condition - with caveats: see post 6 and ps in post 14

    M.
  9. Re: Break Revenue Down By Month - Start End Date and Amount

    Maybe...

    =IF(AND($B2>=D$1,$A2<=DATE(YEAR(D$1),MONTH(D$1)+1,0)),MIN($B2,EOMONTH(D$1,0))-MAX($A2,D$1-1),0)*$C2/($B2-$A2)

    M.
  10. Replies
    3
    Views
    53

    Re: Sequence flag

    You are welcome. Thanks for the feedback.

    M.
  11. Replies
    3
    Views
    53

    Re: Sequence flag

    Counting 15 followed by 9
    =SUMPRODUCT(--(B2:AE2=15),--(C2:AF2=9))

    M.
  12. Re: Index Match Array inconsistent data

    You are welcome. Thanks for the feedback.

    To clean up your data take a look at
    http://dmcritchie.mvps.org/excel/join.htm#trimall

    M.
  13. Re: Index Match Array inconsistent data

    Check if really AF51=AF52 and if AH51=AH52.
    Try in empty cells
    =AF51=AF52
    =AH51=AH52

    Both should return TRUE, otherwise i suspect there are extraneous characters (spaces?) in your data.

    M.
  14. Re: Distinct Result from Multiple Countif

    Maybe... (using rows 2:537802 as in your post 11)



    A
    B
    C
    D
    E
    F
  15. Re: Distinct Result from Multiple Countif

    I can't see any data in column D (???)
    Please, try to provide a data sample with data in column D, as you did in post 1, criteria and expected result.

    M.
  16. Re: Distinct Result from Multiple Countif

    Sorry, I'm not fully understanding what you need. Actually with 600K rows a complex formula should not be a good solution.

    Maybe if you better describe your data, criteria and desired results,...
  17. Replies
    11
    Views
    177

    Re: Index Match Help

    You are welcome. Glad we could help :)

    M.
  18. Re: Distinct Result from Multiple Countif

    Something like this



    A
    B
    C
    D
    E
    F
  19. Re: Distinct Result from Multiple Countif

    What array formula did you try? I think the performance with 5K rows would be OK using an array formula to count unique occurrences of numbers like this:
    =SUM(IF(FREQUENCY(....),1)) ...
  20. Re: MATCH/INDEX: Confirm a "Specific" Text, if any returns differ mark final return value as "No", if all match mark as "Yes"

    Maybe...

    =IF(COUNTIFS('PRPO CLN'!$E$2:$E$5000,A1,'PRPO CLN'!L2:L5000,"Fully Released"),"Yes","No")

    M.
  21. Replies
    11
    Views
    177

    Re: Index Match Help

    Try

    B2
    =INDEX(tbl_Item[VERIFY],MATCH(1,INDEX(--ISNUMBER(SEARCH(tbl_Item[ITEM ID],[@[Folder ID]])),),0))

    M.
  22. Re: SUMIF/SUMPRODUCT across multiple tables question

    You are welcome. Thanks for the feedback.

    M.
  23. Replies
    11
    Views
    177

    Re: Index Match Help

    Maybe...



    A
    B
    C
    D
    E
    F
  24. Re: SUMIF/SUMPRODUCT across multiple tables question

    >0 is necessary because in my example row2 has two instances of "ado". It avoids double counting of this row.

    See the formula suggested by Fluff - it also works and doesn't require >0

    M.
  25. Re: SUMIF/SUMPRODUCT across multiple tables question

    See if this example with a small data sample helps


    A
    B
    C
    D
    E
    F
    G
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4