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

Thread Starter
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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

Thread Starter
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
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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

Thread Starter
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.
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
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

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
BTW, you don't have all the nominal codes in the Results sheet, is this intentional?
 

greigorius

Thread Starter
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
 

Attachments

greigorius

Thread Starter
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.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,456
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

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Hij etaf, I think that's the solution I needed for our poster.
It's quick and versatile, thanks (y)

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
Joined
Oct 2, 2003
Messages
65,456
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

Thread Starter
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.
 
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top