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