sum up monthly figures.

Hi,

without looking at your workbook, it sounds that you need to learn about Pivot Tables. These are a great tool to summarise data that is in a list form. Pivot tables are not hard to learn. Go to www.contextures.com for some great articles, tutorials and tips.

cheers

Using your actual file I would make the following suggestions

1 - use Concatenation @ source to negate need for expensive Array formulae

2 - use LOOKUP to negate need for double evaluation on Monthly Usage sheet

Re point 1...

essentially you're looking to conduct a multi condition Summation, pre XL2007 this limits you to Arrays & SUMPRODUCT both of which are expensive in terms of performance and ill advised in large models (ie where the number of cals is remotely extensive).
You can use Concatenation to create a "key" which then circumvents the need for these approaches given the condition count reverts to one and thus the standard/efficient SUMIF/COUNTIF approaches will suffice. Using your file:

Consumables Sheet

Please Login or Register  to view this content.

(obviously this does not have to be in Col F, it can be out of sight / hidden etc - this is purely for sake of demo)

what the above is doing is creating a key whereby the date value is adjusted to be 1st of month for all transactions and the code is concatenated to the date creating a month:code key.... this removes need for Array/Sumproduct such that:

Monthly Sheet

Please Login or Register  to view this content.

the above simply conducts a SUMIF whereby the values in the new concatenation column (F) match the Month:Code combination for a given row on the summary sheet.

Re point 2...

I noted that you're using a double evaluation process to return description strings from an external file (to handle errors), eg:

Please Login or Register  to view this content.

If the values being returned are always strings (not mix of numbers and strings) and descriptions never > 255 chars in length you can avoid the need for double evaluation by using LOOKUP based approach, eg:

Please Login or Register  to view this content.

You can change the Null ("") to be whatever text you want to appear when the VLOOKUP returns an Error.

I too would suggest a PivotTable grouped by Months