Results 1 to 10 of 10

20050922, 01:49 #1
 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

20050922, 02:01 #2
 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>

20050922, 02:11 #3
 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

20050922, 11:43 #4
 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

20050922, 16:54 #5
 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 = 012250, D5 = 012250. 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 = 01611114310, D24 = 01611114240. 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 01611114310, 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

20050922, 17:18 #6
 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

20050922, 19:47 #7
 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.

20050922, 23:09 #8
 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

20050922, 23:16 #9
 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%"

20050922, 23:33 #10
 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!