Formula with multiple conditions that sums the column

[Helliott99]

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?

 

[Helliott99]

Fee Frequency
40 An
40 An
60 Tri
60 Tri
20 Bi
20 Bi

140 would be the total that I was look for from a formula at the bottom of the column. The formula would have to divide 60 by 3 and add it to the total and divide 20 by 2 and add it to the total and just add the annual fees as is to the total.

 

[Helliott99]

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.

I was looking not to have to add any columns because I have other things going on in the sheet that another column would mess up.

 

[Aladin Akyurek]

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.

Why not provide a tiny (10 rows) sample?

 

[Helliott99]

Fee Frequency
40 An
40 An
60 Tri
60 Tri
20 Bi
20 Bi

140 would be the total that I am looking for the formula to calculate at the bottom of the column. Therefore the formula would need determine the frequency type and then divide 60 by 3 and add 20 to the sum and also divide 20 by 2 and add 10 to the sum along with adding all the straight annual fees to get the annual cost of the fees.

 

[Aladin Akyurek]

Fee Frequency
40 An
40 An
60 Tri
60 Tri
20 Bi
20 Bi

140 would be the total that I was look for from a formula at the bottom of the column. The formula would have to divide 60 by 3 and add it to the total and divide 20 by 2 and add it to the total and just add the annual fees as is to the total.

One way:

=SUMPRODUCT(A2:A7,LOOKUP(B2:B7,{"an";"bi";"tri"},{1;0.5;0.333333333333333}))

The LOOKUP usage here requires that B2:B7 should not house nothing else but "an", "bi", and "tri".

Otherwise, if you have the conversion table say in D1:E3 like below:

an	1
bi	0.5
tri	0.333333

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

we can also invoke:

=SUMPRODUCT(SUMIF(D1:D3,B2:B7,E1:E3),A2:A7)

 

[WarPigl3t]

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?

I'm not entirely sure what you are saying here, but I think you are saying that the words "annually", "biannually", and "triennually" turn into upper case. That's okay. Just change feeType = Range("I" & i).Value into this.

feeType = LOWER(Range("I" & i).Value)

That way it doesn't matter what case it is.

 

[WarPigl3t]

Sorry but I think my last message has wrong code. It should be this...

feeType = LCase(Range("I" & i).Value)

 

[billszysz]

Another one

=SUM(A2:A7/MATCH(B2:B7,{"An";"Bi";"Tri"},0))

Confirm by CTRL+SHIFT+ENTER

 

[Helliott99]

Thanks for your help everyone. I played with my own solution and found that this works for me and what I need:
=SUMIFS(H:H,I:I,"Tri")/3+SUMSIF(H:H,I:I,"Bi"/2+SUMIFS(H;H,I:I,"An")
There is probably something way more efficient and maybe smarter but this is working for now. Thanks again for all your help.

 

[Aladin Akyurek]

Thanks for your help everyone. I played with my own solution and found that this works for me and what I need:
=SUMIFS(H:H,I:I,"Tri")/3+SUMSIF(H:H,I:I,"Bi"/2+SUMIFS(H;H,I:I,"An")
There is probably something way more efficient and maybe smarter but this is working for now. Thanks again for all your help.

That looks ok. But what have you done with the SumProduct/SumIfs solution?