morrisco

New Member
Joined
Aug 5, 2011
Messages
3
I'm working on a balance sheet. It basically works around one main transaction table where I classify each transaction according to its value, the currency in which it was paid, the month in which it happened and the type of transaction. The following is a formula for a table that is derived from the contents of the transaction table.

This formula is in an expense breakdown table where I used a SUMIFS formula to sort the transactions. The first infromation in the formula is the range of the cell that contain the information it will finally add up, after the first coma we find the range where it will look for a criteria and after the second coma we find the criteria itself. So in this case the formula wants to add up some information from the column of the transactions table that contains the [Value] of each transaction, but it will only add the information in the rows of that column that also meet the following criteria: 1) [Mon] column (represents currency) =USD 2) [type] column=food 3) Value column has a negative value and 4)Month column=AUG(august)

=
ABS(SUMIFS(Table[Value],Table4[Mon],"=USD",Table4[Type],"=Food",Table4[Value],"<0",Table4[M],"=AUG"))


The formula works fine but what I want to do is replace the final part of the final criteria so that instead of being specifically AUG it will be a cell number where there is a dropdown list with the different months so if I should pick JAN the formula would stop looking for AUG transactions and would display JAN transactions. So it would look like this:
=ABS(SUMIFS(Table[Value],Table4[Mon],"=USD",Table4[Type],"=Food",Table4[Value],"<0",Table4[M],"=(B19)"))

(B19) is the cell, located in the second sheet of the excel document where the dropdown list is located.


The problem is that when I do this the formula always returns a value of 0. Even if I pick AUG from the drop-down list I get a result of 0 when if I just type AUG I get the correct value that I want. How can I fix this? How can I get the formula to realize that the contents in the cell with the drop down list are the same as if I type them?

P.S. the same happens if I try to change the word for for a cell that contains the word food and I tried using MATCH and LOOKUP formulas nut I don't really understand the results they give me so if you think it can be solved using one of those formulas please explain. I am also get more suspicious that the problem may be cuased because of the sheet difference but I still don't know how to fix it.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi
Welcome to the board

Try:

=ABS(SUMIFS(Table[Value],Table4[Mon],"=USD",Table4[Type],"=Food",Table4[Value],"<0",Table4[M],B19))
 

morrisco

New Member
Joined
Aug 5, 2011
Messages
3
Thankyou your correction worked perfectly. I knew it wasn't such a big mistake cuasing the error but I just couldn't find it.
 

Forum statistics

Threads
1,136,968
Messages
5,678,868
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top