Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If Then and Percentage formula (2002 SP3)

    It's a strange question but I was wondering if on the attached spreadsheet there's an easier way to do the following rather than going through each row individually..

    1. If A2 exactly matches D2, then I would like add B2 and E2 and have the total of that timed by 40% appear in G2.
    2. If A2 DOES NOT match D2, then I would like the formula to look for the match in column D, then add B2 and E2 and have the total of that timed by 40% appear in G2. (Example is Row 24)
    3. If A2 to A4 are exact and D2 to D4 are exact, then I would like add the totals of each set and have the total of that timed by 40% appear in G2.

    I'm currently doing this the long way but if there's an easier way, I'd sure appreciate it!

    Thanks.
    Pooja

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If Then and Percentage formula (2002 SP3)

    I don't understand, 1. and 2. say that whether A2 matches D2 or not, you want to add B2 and E2 and multiply the sum with 40%. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: If Then and Percentage formula (2002 SP3)

    I don't understand 2 or 3. What do you mean by "then I would like the formula to look for the match in column D" - what is the match criteria? What do you mean by "If A2 to A4 are exact and D2 to D4 are exact, then I would like add the totals of each set " - what do you mean by "set", and do you ignore the rest of the accounts data?

    It might help if you explain your objective - are you trying to find 40% of the combined two months total for each G/L account? That could be done with some SUMIF formulas and/or array formulas - see Chip Pearson's Extracting Duplicate and Unique Values Page.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If Then and Percentage formula (2002 SP3)

    Perhaps are you looking for this in D2:
    =(SUMIF($A$2:$A$312,$A2,$B$2:$B$312)+SUMIF($D$2:$D $319,$A2,$E$2:$E$319))*40%

    Or even:
    =IF(A2=A1,"",(SUMIF($A$2:$A$312,$A2,$B$2:$B$312)+S UMIF($D$2:$D$319,$A2,$E$2:$E$319))*40%)

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then and Percentage formula (2002 SP3)

    No, sorry. A little synopsis to explain it a bit better. The information in Columns A and D are General Ledger Account Numbers imported into Excel via a different program. Now, for our purposes in accounting, we only need one line for each GL Account and related funds. Column A is everything up to June 30th (the end of the fiscal year), Column D is everything up to now in the current fiscal year.

    So for a couple of examples:
    Row 5: A5 = 01-2250, D5 = 01-2250. Therefore the corresponding amounts in B5 (2,165.12) and E5 (2,362.99) can be added together and 40% of that can show up in G5 (1,811.24).

    Row 24: A24 = 01-61111-4310, D24 = 01-61111-4240. Therefore, I can't add the corresponding amounts together. I have to look for the Account represented in A24 in Column D and I find it in D26 and D27. So now for GL Account 01-61111-4310, I need the values in cells B24, B25, E26, and E27 added together and 40% of the total to be reflected in G24.

    If the account in Column D does not have a corresponding match in Column A, then just the 40% of the total associated with it in Column E can show in Column G.

    I color coded it a bit better so you can sort of see what I'm talking about and hopefully understand it a bit more. [img]/forums/images/smilies/smile.gif[/img]

    Thank you all for your help!
    Pooja

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: If Then and Percentage formula (2002 SP3)

    I am afraid I do not understand any better. From your latest description I would still give the same formulas. Could you attach a sample with the logic and the values you want (especially the ones you point out).

    Why (eg is) G2 be -133,171 (from all the rows) while G3 and G4 only use the current row?

    If you put the calc you want in the cell we can see better the logic...

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If Then and Percentage formula (2002 SP3)

    I don't think this is easily done using formulas only. Try this macro:

    Sub ListUnique()
    Dim col As New Collection
    Dim lngRow As Long
    On Error Resume Next
    For lngRow = 2 To Range("A65536").End(xlUp).Row
    col.Add Range("A" & lngRow), Range("A" & lngRow)
    Next lngRow
    For lngRow = 2 To Range("D65536").End(xlUp).Row
    col.Add Range("D" & lngRow), Range("D" & lngRow)
    Next lngRow
    Range("G2:H55536").ClearContents
    For lngRow = 2 To col.Count + 1
    Range("G" & lngRow) = col(lngRow - 1)
    Range("H" & lngRow).Formula = _
    "=(SUMIF(A:A,G" & lngRow & ",B:[img]/forums/images/smilies/cool.gif[/img]+" & _
    "SUMIF(D,G" & lngRow & ",E:E))*40%"
    Next lngRow
    Set col = Nothing
    End Sub

    It uses a collection to create a list of unique accounts in column G, then creates SUMIF formulas in column H.

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then and Percentage formula (2002 SP3)

    Okay, that's some skill you have Hans! It works perfectly. Thank you very much!

    Just one last question, if I want to modify this macro to add the totals of Column B to 40% of the totals in Column E, is it easily doable?

    Example Again:
    B2 = (2,737.14)
    E2 = (2,488.80)
    H2 should equal = (3,732.66) by using the formula =(E2*40%)+B2

    Only if possible. Thank you again. You've just saved me hours of work.
    Pooja

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If Then and Percentage formula (2002 SP3)

    If I understand you correctly, you only want to multiply the values in column E by 40%, not those in E. To do that, change the lines

    Range("H" & lngRow).Formula = _
    "=(SUMIF(A:A,G" & lngRow & ",B:[img]/forums/images/smilies/cool.gif[/img]+" & _
    "SUMIF(D,G" & lngRow & ",E:E))*40%"

    to

    Range("H" & lngRow).Formula = _
    "=SUMIF(A:A,G" & lngRow & ",B:[img]/forums/images/smilies/cool.gif[/img]+" & _
    "SUMIF(D,G" & lngRow & ",E:E)*40%"

  10. #10
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Then and Percentage formula (2002 SP3)

    Perfect! Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •