Forum Discussion

Copper Contributor

Mar 18, 2019

Impossible formula? Possibly COUNTIF Function

Hello, 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: =...

COUNTIFS function

Silver Contributor

Mar 19, 2019

The formula you need to return your desired result is:
=SUMPRODUCT(
COUNTIF(U766:U6886,
“<“&
V766:V6886*0.50))

ZachStone9
Copper Contributor
Mar 20, 2019
Twifoo

I tried this and it does not seem to produce the calculation, even though it is a valid formula. Maybe I am still doing something incorrectly?
- Pvberg
  Copper Contributor
  Mar 21, 2019
  ZachStone9
  
  Hello:
  
  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.

Resources