Correct use of Sumifs function

[KPAW]

I have an excel workbook with two spreadsheets. Sheet 1 is for data entry while Sheet 2 is for a summarized report.

On sheet 1 we have the data entry as shown
Added Subtracted
Account Oct-10 Oct-10
Furniture 10,000.00
Computers 5000
Stationery 5000

In this extract, $10,000 is taken from the furniture account and distributed evenly among the computers and stationery accounts.

I want to show this information on a summary sheet in sheet 2. The extract is shown:

Summary Report
Added Subtracted
Account Oct-10 Oct-10
Furniture 10,000.00
Computers 5000
Stationery 5000

The reality is that during any month in the 12 months fiscal year which starts in October 2010, money can be added or subtracted multiple times from any account and distributed to one or many accounts.

My challenge is to summarize the funds that are taken or added to a particular account in a particular month of the fiscal year.

I have tried using the sumifs function but was unsuccesful to get the result I need.

Any assistance is greatly appreciated.

 

[dfenton21]

Hi There,

If possible, I would advise having the date in a seperate column to the costs. It makes calculation of these stats much easier.
Also, rather than having an added/subtracted cell, simply use positive and negative numbers in your account.

With that in mind, the formula below should be of help to you, where Col A contains item description, Col B contains costs and Col C contains the date range you want to report on.

=SUMPRODUCT(--(A1:A9="Computers"),--(C1:C9="Oct-10 Oct-10"), B1:B9)

It is a sumproduct formula which can essentially work like a sumif with multiple criteria.

(Sorry I primarily use Excel 2003, so I'm not familar with the simiffs function)

I hope this helps, and please let me know if you need further assistence.

Damien