Multiple If Statements

@ChrisMcKay

I have found quite a bit of useful information to make Nested If statements; however, they are set-up 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.

ricki

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

Paul Newcome

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.

@ChrisMcKay

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,

@ChrisMcKay

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)

ricki

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

ricki

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)

@ChrisMcKay

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?

 

@ChrisMcKay

IF(AND({PO Tracking Range 7} = Firm2, {PO Tracking Range 9} = [Building Code]2), {Encumbrance}, 0)

Another stab at it gets me #invalidoperation.

ricki

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?

 

ricki

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

@ChrisMcKay

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.

ricki

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

 

@ChrisMcKay

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. 

ricki

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

ssanchez

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