sumif syntax?

[Anonymous]

Hi, I don’t understand in 2.11 this syntax: =SUMIF(OpProj!$J$12:$EK$12,”<=”&5,OpProj!$J$27:$EK$27)/5/K13.

What does the quotation marks and ampersand mean? “<=”&5. I’ve never seen this syntax before.

Thank you!

[Michael Belasco]

Hi Wesley,

Thanks for the question. I could have simply done “<=5" in quotation marks and it actually would have worked as well. Although not incorrect, I was a bit on autopilot and this is the syntax you use if you were to reference a cell instead of a hard coded number. For example, let’s say I wanted to play around with the number of years I wanted to use to get the average development yield, and to do that I wanted to make cell C12 the input to which I can change the assumption. If I wrote =SUMIF(OpProj!$J$12:$EK$12,”<=C12",OpProj!$J$27:$EK$27), it would not reference C12 and would just return 0 because excel wouldn’t recognize this as written. To do this, you will need to put <= in quotation marks, type an ampersand and click on the cell you want to reference. So in this case, we would write =SUMIF(OpProj!$J$12:$EK$12,””<="&C12,OpProj!$J$27:$EK$27). This goes for <, >, >= as well.

As for the quotation marks in general, with the =SumIf function, excel won’t understand >, <, <=, >= without them. If you tried one of these without the quotation marks and hit enter an error box appears.

Hope this answers your question.

Thanks,

MB

[Anonymous]

Oh okay, thank you Michael!

[Author]

Posts