# Solved: Excel 2007 - Conditional SUM formula problem

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### greigorius

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

#### Keebellah

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.

#### greigorius

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

#### Keebellah

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

#### greigorius

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.

#### Attachments

• 37 KB Views: 131

#### Keebellah

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

#### Attachments

• 34 KB Views: 123

#### Keebellah

Hans
BTW, you don't have all the nominal codes in the Results sheet, is this intentional?

#### greigorius

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

#### Attachments

• 53.5 KB Views: 94

Wayne
Moderator

#### greigorius

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.

#### etaf

Wayne
Moderator
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

#### Attachments

• 48 KB Views: 113

#### Keebellah

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.

#### etaf

Wayne
Moderator
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

#### greigorius

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.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On