Summing fields depending on other field values

[bishman]

Hi, First post here - please be gentle!

I've got some VBA code that outputs some results as follows into an Excel sheet:

Name Class Grade Grade total
John Biology 80
John Biology 76
John Maths 65
John Maths 54
Charles Biology 23
Charles Maths 45
etc etc

What I want to do is write some code that will calculate the grade totals and output them to the grade total column. If we take John and Biology as an example, I need the logic to be able to put "80" in the grade total for the first "John, Biology" row, and "156" in the second occurrence of the "John, Biology" row - that is, 80 + 76.

If anyone can give me a pointer that would be great!
Cheers

 

[TonyJollans]

Hi bishman,

Assuming your data is in columns A to C,

In D1 put

=A1&B1

In E1 put

=SUMIF(D$1:D1,D1,C$1:C1)

.. and copy dow as far as your data goes. You can then hide column D if it gets in your way.

Enjoy,
Tony

 

[bishman]

Hi Tony,

Thanks for the quick reply. Unfortunately, each time the VBA is run, there will be a differing number of rows in the excel sheet. I would really like to be able to automate this process from VBA. Is it possible?

Thanks again

 

[bishman]

Hi again Tony,

Managed to sort this now. I have used your SUMIF suggestion, and am now dynamically contructing the SUMIF string within a loop in my VBA code, and pasting this string straight into the necessary cells in the "results" sheet.

Thanks again,
bishman

 

[TonyJollans]

Hi bishman,

You can add these formulae for as far as your data goes, in VBA, by using:

Range([D1], [C65536].End(xlUp).Offset(0, 1)).FormulaR1C1 = "=RC[-3]&RC[-2]"
Range([E1], [C65536].End(xlUp).Offset(0, 2)).FormulaR1C1 = "=SUMIF(R1C[-1]:RC[-1],RC[-1],R1C[-2]:RC[-2])"

Enjoy,
Tony

 

[shaggi]

Hi Bishman,
Tony is bang on as usual,but unless it is absolutely required to use code, have a look at DBase formulas such as sumproduct, I was pointed in that direction on an earlier thread and they work a treat....