# Thread: If Then and Percentage formula (2002 SP3)

1. ## 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. ## 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. ## 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.

4. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•