Multiple If Statements
I have found quite a bit of useful information to make Nested If statements; however, they are setup to be "if this, then that or if this, then that". What I need is: "If this is equal to this, and this is equal to this, then this"
Here is what I have come up with:
=IF(AND(Firm2 = {Building Code}, Firm = {Firm},), {Encumbrance},0)
What I am trying to accomplish is if a two criteria are met, then the encumbered amount is brought over to this sheet.
Comments

i am not sure i understand the values you are trying to compare to but the AND should work in the if statement ... it may just be an issue of your statement having an extra comma after {Firm} ..... i just ran this very simple example and it worked fine ... maybe that will help you
=IF(AND(([Column3]11 = 4), ([Column3]10 = 3)), "A", "B")
Where A is what i want if all conditions are true and B if all conditions are not true

Your cross sheet references {Building Code} and {Firm} may need to have specific ranges established. I generally suggest using the "Reference Another Sheet" link in the formula help box. What you will typically get from that is {Building Code Range #} or {Firm Range #}. See screenshots below.

I am able to replicate your formula, it appears the issue I am having is with the "value_if_true" being a specific cell. If I change the "value_if_true" to "1" or any other quoted value it works. Is this a limitation of the formula?
My desired outcome is to have all project related financial information in one sheet, with specific project information filtered to individual sheets.
Thanks,

Thanks Paul. I should have clarified, I changed the name of the columns to make it easier on me.
I updated the formula and now I get "invalid operation".
=IF(AND(Firm2 = {Firm}, [Building Code]2 = {Building Code}), {Encumberance}, 0)

The formula definitely allows cell referencing … I just tried the following and it worked perfectly
=IF(AND(([Column3]11 = 4), ([Column3]10 = 3)), [Column4]9, [Column5]7)
also, the following works (for even more referencing)
=IF(AND(([Column3]11 = [Column3]9), ([Column3]10 = [Column3]5)), [Column4]9, [Column5]7)
I think the issue is how you are referencing the cells ….. sometimes you specify a name, sometimes you use {} and sometimes you don't …. sometimes you use a number and sometimes you dont
I think in your cell reference you just need to specify exactly which cell you are interested in

I also just tried the following referencing some cells from a different smartsheet and it also works
IF(AND({Performance Testing Range 1} = [Column3]11, {Performance Testing Range 2}=[Column3]12),[Column4]7,[Column5]7)

I think I have replicated your formula, above. Although, I am still getting the "#UNPARSABLE" error.
IF(AND({PO Tracking Range 7} = [Firm1], {PO Tracking Range 9} = [Building Code]1), 20, 0)
Do you spot an error in the logic?
One thing I noticed in your above, is that the 'if true' is a single cell. Can that be a range, if the corresponding rows are true?

IF(AND({PO Tracking Range 7} = Firm2, {PO Tracking Range 9} = [Building Code]2), {Encumbrance}, 0)
Another stab at it gets me #invalidoperation.

I think the issue is you are trying to compare a whole column to a single cell
it looks like you are saying if the PO Tracking range  row 7's value from a different sheet = the entire column firm 2 from this sheet? do you mean firm row 2? Or is it that you have a column called firm and you want firm row 2? in that case try [firm]2
Also, not understanding what you are trying to return if the AND is true? the entire encumbrance column from the other sheet into a single cell in this sheet? why is there no number by encumbrance?
What exactly are the column names in your two sheets? and which rows are you trying to reference? for each of those cells?

if your column is called firm then I think you need [firm]1

Hopefully this clarifies it. The first screenshot is an individual project invoice sheet. It displays all the invoices that have been paid, for that specific project. The second screenshot shows all of the encumbrances for a client. The encumbrance sheet could have over 1,000 line items, for 100 various projects. Each project would have an individual invoice tracking sheet.
Once an invoice is paid, the name of the firm and project number are entered. The formula should then match those two criteria with the encumbrance sheet and display the total encumbrance. This is why the encumbrance sheet is a range for firm and project number.

It sounds like you are looking for the sum from the other sheet. A range is not the same thing as a sum. A range is just a list of cells which is why your formula is returning an error. You could take the sum of the range, an average, a count, a min, a max, etc ….
I think you can get what you are looking for with the sumifs formula
This formula returned correctly on my test smartsheet
=SUMIFS([Column3]:[Column3], [Column2]:[Column2], [Column2]1, TestDate:TestDate, [Column5]7)
Column3 would be the column name you want to sum (I think {amount} referenced in the second sheet in your case)
Column2 is your first condition and [column2]1 would be the matching value for that condition. I think in your case it would be
,{Firm}:{Firm},[Firm]1 depending on how you would reference the sheet
Then the TestDate is the other column I want to match on with [column5]7 being the matching value in that column

Regarding firm2, yes that’s correct. I believe brackets don’t work because there isn’t a space in the column title. Every time I put brackets, it would remove.
I’m trying to return the encumbrance from the row, if it met the criteria in the other columns. I think that’s where the IF statement is having issues. If I make that a single cell, it will work.
I was able to solve my problem by adding a column that sums both firm and project number. Then, doing the same on my encumbrance sheet. This removes the need for to IF statements. Then I used Vlookup to find the combination of FirmProject and the corresponding row that had the encumbrance value I was looking for.
After your last comment, it was clear to me that If_true can’t be a range. I was hoping it would choose the corresponding cell to the IF statements.
I appreciate yiur help.

I am glad you found a solution that worked. As mentioned in one of the other replies i think that the sumifs formula would get what you want as well

Hi All I am trying to do the following,
If my IF=([Average Variance] is between .07 and .07 then "Up", if not then "Down"
I am trying to show the overall trend for our team. I would like to do it monthly and create a line graph showing performance.
Thank you,
Sam
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!