Tech Support Guy banner
Status
Not open for further replies.
1 - 17 of 17 Posts

·
Registered
Joined
·
14 Posts
Discussion Starter · #1 ·
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
 

·
Trusted Advisor
Joined
·
6,911 Posts
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.
 

·
Registered
Joined
·
14 Posts
Discussion Starter · #3 ·
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
 

·
Trusted Advisor
Joined
·
6,911 Posts
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
 

·
Registered
Joined
·
14 Posts
Discussion Starter · #5 ·
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

·
Trusted Advisor
Joined
·
6,911 Posts
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

·
Trusted Advisor
Joined
·
6,911 Posts
BTW, you don't have all the nominal codes in the Results sheet, is this intentional?
 

·
Registered
Joined
·
14 Posts
Discussion Starter · #10 ·
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.
 

·
Super Moderator
Joined
·
65,526 Posts
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

·
Trusted Advisor
Joined
·
6,911 Posts
Hij etaf, I think that's the solution I needed for our poster.
It's quick and versatile, thanks :up:

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. :)
 

·
Super Moderator
Joined
·
65,526 Posts
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
 

·
Super Moderator
Joined
·
65,526 Posts

·
Trusted Advisor
Joined
·
6,911 Posts
I did some coding a reproduced your Reulsts screen in the form of the pivot table.

Just double click on cell B1 of Sheets Results to refresh.

Condition is that the Data sheet starts on row 6 with first entry on row 7 or 8 as is now.
It was just fun to do.

The table will expand or reduce denpending on the number of month's and therefore also the text in the last column

8-months 6-months whatever applies.
It was just fun to do, and like I say, I like the 'brain gymnastics' ;)
 

Attachments

·
Super Moderator
Joined
·
65,526 Posts
nice work - i notice on the original OP example he grouped the codes and sub-totalled - been playing to do that in a pivot and not so easy on the grouping
 
1 - 17 of 17 Posts
Status
Not open for further replies.
Top