Formula with multiple conditions that sums the column

H

Helliott99

New Member
Joined
Oct 23, 2014
Messages
13
I have a column (G2:G168) with fee amounts in them. The fee is based annually, biannually or triennially - this designated in Column I2:I168. I want the to total of the fee column to show the "annual cost" of the fees. For example cell G2 might need to be divided by 2 or 3 depending on whether or not I2 said Bi or Tri in it before it was included in the sum. Is there a way to do this?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Sure, without getting crazy, add a third column and use an nested if then like this maybe....=IF(J8="tri",I8/3,IF(J8="Bi",I8/2,I8))

Sorry, I didn't use your columns but hopefully you can make sense of it. Pls test it.
 
Upvote 0
Code: 
Sub myMacro()
    lastRow = Range("G" & Rows.Count).End(xlup).Row
    i = 2
    Do Until i > lastRow
        feeType = Range("I" & i).Value
        feeAmount = Range("G" & i).Value
        If feeType = "annually" Then
            mySum = mySum + feeAmount
        End If
        If feeType = "biannually" Then
            mySum = mySum + (feeAmount / 2)
        End If
        If feeType = "triennially" Then
            mySum = mySum + (feeAmount / 3)
        End If
        i = i + 1
    Loop

'Where do you want your mySum to output?  I'm putting it in cell A1.  Change it.
Range("A1").Value = mySum
End Sub
If you would like to put it in a formula, you can do this instead...
Code: 
Function myFunction()
    Application.Volatile
    lastRow = Range("G" & Rows.Count).End(xlup).Row
    i = 2
    Do Until i > lastRow
        feeType = Range("I" & i).Value
        feeAmount = Range("G" & i).Value
        If feeType = "annually" Then
            mySum = mySum + feeAmount
        End If
        If feeType = "biannually" Then
            mySum = mySum + (feeAmount / 2)
        End If
        If feeType = "triennially" Then
            mySum = mySum + (feeAmount / 3)
        End If
        i = i + 1
    Loop
myFunction = mySum
End Function
Type this into any cell you want once you paste this code into your macro list.
=myFunction()
 
Last edited:
Upvote 0
Helliott99 said:
I have a column (G2:G168) with fee amounts in them. The fee is based annually, biannually or triennially - this designated in Column I2:I168. I want the to total of the fee column to show the "annual cost" of the fees. For example cell G2 might need to be divided by 2 or 3 depending on whether or not I2 said Bi or Tri in it before it was included in the sum. Is there a way to do this?
Click to expand...

=SUMIFS(G:G,I:I,"Bi")

would sum the "biannual cost".
 
Upvote 0
Thanks so much. This does seem to work on my practice version but when I put the myfunction version into my actual document the lower case i turns into and upper case I automatically in the code and then I get a $0 in my total cell? Do you have any suggestions to fix that problem?
 
Upvote 0
I think I know why the lower case i changes to an upper case I automatically in myfunction macro - I have another macro in this worksheet that defines I = s1.Cells(Row.Count, "C".End(X1up).row. I got the other macro from somewhere else to solve a different issue. I have no idea how to rectify the problem though. And I was wrong, it doesn't return a $0 to my total cell -I get a #value! error. Any help to solve this would be greatly appreciated. THanks.
 
Upvote 0
The formula that you provided only sum the total Biannual Cost. That is not what I was looking for - I need a sum of the column but it needs to calculate the annual cost for each fee. Therefore, if the Biennial fee was 40, I would need to add 20 to the total for that row not 40 and I don't just want the total of the Bienniel fees, I want the total cost of all the fees (annual, biennial & triennial) on an annual basis.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Sum column exclude cells which have maximum for row
Replies
4
Views
797
Peter_SSs
Peter_SSs
D
  • Solved
Multiply 3 criteria with 2 conditions and sum the results for multiple columns.
Replies
5
Views
578
Doug_B
D
S
  • Solved
sum combined with Xlookup using multiple criteria not working
Replies
1
Views
167
shellwig
S
X
  • Question
SUM results of CHOOSECOLS with FILTER for multiple criteria
Replies
0
Views
629
xcelnovice101
X
P
  • Question
Formula to Sum the values of a column based on multiple criteria
Replies
1
Views
521
AFPathfinder
A

Forum statistics

Threads
1,214,818
Messages
6,121,725
Members
449,049
Latest member
MiguekHeka

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top