VBA and MS Excel - Conditional Sum

Posted by Guy Lecky-Thompson Dec 8, 2006

I discovered that the conditional sum plug-in for Microsoft Excel does not always do what I want. It fails to behave exactly as I expected, and since the help files were not very illuminating, I decided to make my own solution in Visual Basic for Applications.

It turned out that this solution required that I relearn how to build VBA macros in Microsoft Excel, as well as creating a reasonably flexible implementation. Being a good exercise for non-programmers and programmers prototyping algorithms and applications, I thought I'd share it with you.

The premise is that I needed to sum rows of data conditionally, based on the contents of those rows. One cell was the number to be added to the rolling sum, and the other three or four cells were used to decide whether or not to update the total or not.

The crunch was that the Active Sheet contained the source data for making those decisions. So, each cell had to contain a total that was conditionally arrived at by examining neighboring cells (luckily fixed as the first cell in the row, and column headings). To cap it all, only certain areas of the Active Sheet needed to be updated, so the user had to be queried at the start of the process.

I'll give an example:

The Active Sheet contains (let's say) fruits down the left hand side – 'Apples', 'Oranges', 'Pears' and so on. The column headings give sizes of the fruits – 'Small', 'Medium', 'Large' and so on. The Data sheet contains sales entries – the date, number, type and size.

If we want to sum sales entries for all the small, medium, and large apples, from a list of varying entries, using Excel, I found it best to use a VBA macro. Conditional sum doesn't work, for a variety of reasons.

The solution to this is explained in computerprogramming.suite101.com/article.cfm/starting_with_vba_for_ms_excel. Have fun!