# Solved: Excel 2007 - Conditional SUM formula problem

Discussion in 'Business Applications' started by greigorius, Jan 12, 2011.

Not open for further replies.

Joined:
Jul 12, 2009
Messages:
14
Hi,

I am having trouble getting results from a formula that is trying to return the sum of numerous values in a field once 2 conditions are met. I would say I have between novice and intermediate experience with excel formulas so I hope I can explain it clearly.

Worksheet 1: results

Worksheet 2: data
Column 1 (month): April, April, April, May, June, June, July, July, etc...
Column 2 (code): 4000, 6500, 4902, 4902, 3002, 4000, etc...
Column 3 (amount): £0, £0, £3598, £725, £0, £2212, £0, etc...

I want to get the sum of the amounts in Column 3 for the code in Column 2 in a specific month. For example, how much was spent in April for code 4902?

I am working with this formula
=SUM(IF(Worksheet2!\$A\$6:\$A\$245="April",IF(Worksheet2!\$B\$6:\$B\$246=4902,Worksheet2!\$C\$6:\$C\$246,0),0))

However, this is returning errors.

Can anyone help solve this one, perhaps with a simpler formula?

Thanks

Joined:
Mar 27, 2008
Messages:
6,597
First Name:
Hans
I don't think there is a conditional formula that does this in one step.
I don't know if a macro will be an option for you since these are not always allowed on some networks.
Maybe you'll have to breakdown the selections into two or three steps and then add the found results.
I would suggest an extra table with let's say the names of the month accrowss and the list of codes as rows (unique values)
Then I think a a macro would be the best solution to do all the calculating.
drop a line and let us know if this would be an option.

Joined:
Jul 12, 2009
Messages:
14
Hi,
Thanks for the speedy response. I'm not on a network so I guess a macro can be used, only I don't know a lot about them. If it's as straight forward as copying code into a new module in Visual Basic then I will be happy to give it a go. However, I am also interested in breaking the information down further into more tables if you have suggestions for this method too.
Thanks

Joined:
Mar 27, 2008
Messages:
6,597
First Name:
Hans
I'll need your input to know what you need.

I'll see if I can put together a simple macro to show the costs per month per code

Joined:
Jul 12, 2009
Messages:
14
I have attached a sample workbook to help give you an idea of the problem. You'll see that I need the calculation for both credit and debit amounts for each month.

Look forward to seeing what you come up with.

#### Attached Files:

• ###### sample_ledger.xls
File size:
37 KB
Views:
131

Joined:
Mar 27, 2008
Messages:
6,597
First Name:
Hans
I see what you want, I'll edit the code I wrote and see how fast I can put it to work, in the attached file see my first step with the data I took from the post

#### Attached Files:

• ###### greigorius-Book1.xls
File size:
34 KB
Views:
123

Joined:
Mar 27, 2008
Messages:
6,597
First Name:
Hans
BTW, you don't have all the nominal codes in the Results sheet, is this intentional?

Joined:
Jul 12, 2009
Messages:
14
I thought I could just give you a sample and then I could transpose the formula/macro across the rest of the sheet. Anyway, I have attached a version with all nominal codes.
Thanks

File size:
53.5 KB
Views:
94
9. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne

Joined:
Jul 12, 2009
Messages:
14
Hi etaf,
Thanks for the response. This was the first thing I tried but it would only return the number of cells that contained a value and not the sum of those cells...? It's very frustrating because I'm sure there is a simple fix to this.

11. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
i dont want to stop Keebellah work with macros here
You may want to look at the pivot table option - the way you have the datasheet and also the layout you want - lends itself to a Pivot Table

I have attached the table here

#### Attached Files:

• ###### sample_ledger_ETAF.xls
File size:
48 KB
Views:
113

Joined:
Mar 27, 2008
Messages:
6,597
First Name:
Hans
Hij etaf, I think that's the solution I needed for our poster.
It's quick and versatile, thanks

VBA is easy but not always the most indicated since it creates a dependancy.

I'll do some coding just for the 'brain gymnastics' anyway.

13. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
Hi Keebellah , it looked like it might be and with some changes to format you can make the sheet look quite presentable also easily updated - I'm very rusty on macros - but like to follow your posts to keep my hand in a little

Lets see what the OP has to say, as there maybe more twists which require the macro

Joined:
Jul 12, 2009
Messages:
14
Hi guys,

No hidden twists, this is precisely what I need. I have not used pivot tables so much but know people who swear by them - I can now see why.

Thanks very much.

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne

As Seen On