03-18-2019 04:42 PM
03-18-2019 04:42 PM
I want to create a COUNTIF formula that shows me how many Land Use Designations (LUDs) have building values less than 50% of the total property value. It is basically something like:
ROW U is building value, V is total land value (land and building value combined).
Does this make sense to any Excel wizards out there?
Any help is very much appreciated.
03-18-2019 07:34 PM
I suggest the simplest is probably to set up a helper column. It has the advantage of giving you the number you are looking for while at the same time identify the properties that meet your criteria.
There are a couple ways I could suggest:
Then you can use conditional formating to highlight all the rows that are less than .5
03-18-2019 07:50 PM
03-20-2019 02:52 PM
I have conditioned myself to think in terms of lists or arrays and only on rare occasions do I need to break the problem down to individual cells. In order to evade implicit intersection that will generate single-cell formula I tend to define many data objects of interest using Named formulas, thus 'lowBV' (low value building) could refer to Boolean conditions
= ( LU < V/2 )
or, in order to replace TRUE by 1.
= IF( LU < V/2, 1 ).
This leads to the formula
= COUNT( lowBV )
Conditional formats are a little more trouble because they do not accept arrays as the basis for formatting. Using Dynamic Arrays I had to introduce a second cell-by-cell formula 'highlight?'
=(@LU < @V/2)
03-20-2019 07:40 PM
I don't like long sumproduct and other formulas that for the average user is opaque. The formula that was suggest falls into that category, it leaves the user stuck or the next user that tries to modify the file gets stuck. Less opaque is better. I also try to give advice that anticipates the next problem. Do you really want on the number of LU's that meet criteria? Or will you eventually want a list of problems that meet the criteria or don't meet it? Simply counting using the formula won't answer that question. So I continue to suggest the helper column spreadsheet I posted. But if you insist single formula and haven't found the solution here's what will work: =SUMPRODUCT(--(B2:B20/C2:C20<0.5)) (this uses the example data in the posted spreadsheet. Using your addresses would be =SUMPRODUCT(--(U766:U6886/V766:V6886<0.5)). Note this work because the two -- in front convert the TRUE or FALSE comparison into a ! or 0 all that meet the criteria will have a value of 1, the other 0. The sum part of the formula takes over and adds up all the ones.