Home

Impossible formula? Possibly COUNTIF Function

%3CLINGO-SUB%20id%3D%22lingo-sub-376522%22%20slang%3D%22en-US%22%3EImpossible%20formula%3F%20Possibly%20COUNTIF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-376522%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20create%20a%20COUNTIF%20formula%20that%20shows%20me%20how%20many%20Land%20Use%20Designations%20(LUDs)%20have%20building%20values%20less%20than%2050%25%20of%20the%20total%20property%20value.%20It%20is%20basically%20something%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCOUNTIF(U766%3AU6886%2C%20V%26gt%3B(.5*U))%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EROW%20U%20is%20building%20value%2C%20V%20is%20total%20land%20value%20(land%20and%20building%20value%20combined).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20this%20make%20sense%20to%20any%20Excel%20wizards%20out%20there%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20very%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-376522%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECOUNTIFS%20function%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eformula%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-378037%22%20slang%3D%22en-US%22%3ERe%3A%20Impossible%20formula%3F%20Possibly%20COUNTIF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-378037%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302984%22%20target%3D%22_blank%22%3E%40ZachStone9%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20like%20long%20sumproduct%20and%20other%20formulas%20that%20for%20the%20average%20user%20is%20opaque.%20The%20formula%20that%20was%20suggest%20falls%20into%20that%20category%2C%20it%20leaves%20the%20user%20stuck%20or%20the%20next%20user%20that%20tries%20to%20modify%20the%20file%20gets%20stuck.%20Less%20opaque%20is%20better.%20I%20also%20try%20to%20give%20advice%20that%20anticipates%20the%20next%20problem.%20Do%20you%20really%20want%20on%20the%20number%20of%20LU's%20that%20meet%20criteria%3F%20Or%20will%20you%20eventually%20want%20a%20list%20of%20problems%20that%20meet%20the%20criteria%20or%20don't%20meet%20it%3F%20Simply%20counting%20using%20the%20formula%20won't%20answer%20that%20question.%20So%20I%20continue%20to%20suggest%20the%20helper%20column%20spreadsheet%20I%20posted.%20But%20if%20you%20insist%20single%20formula%20and%20haven't%20found%20the%20solution%20here's%20what%20will%20work%3A%26nbsp%3B%3DSUMPRODUCT(--(B2%3AB20%2FC2%3AC20%26lt%3B0.5))%20(this%20uses%20the%20example%20data%20in%20the%20posted%20spreadsheet.%20Using%20your%20addresses%20would%20be%26nbsp%3B%3DSUMPRODUCT(--(U766%3AU6886%2FV766%3AV6886%26lt%3B0.5)).%20Note%20this%20work%20because%20the%20two%20--%20in%20front%20convert%20the%20TRUE%20or%20FALSE%20comparison%20into%20a%20!%20or%200%20all%20that%20meet%20the%20criteria%20will%20have%20a%20value%20of%201%2C%20the%20other%200.%20The%20sum%20part%20of%20the%20formula%20takes%20over%20and%20adds%20up%20all%20the%20ones.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-377946%22%20slang%3D%22en-US%22%3ERe%3A%20Impossible%20formula%3F%20Possibly%20COUNTIF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-377946%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302984%22%20target%3D%22_blank%22%3E%40ZachStone9%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20conditioned%20myself%20to%20think%20in%20terms%20of%20lists%20or%20arrays%20and%20only%20on%20rare%20occasions%20do%20I%20need%20to%20break%20the%20problem%20down%20to%20individual%20cells.%26nbsp%3B%20In%20order%20to%20evade%20implicit%20intersection%20that%20will%20generate%20single-cell%20formula%20I%20tend%20to%20define%20many%20data%20objects%20of%20interest%20using%20Named%20formulas%2C%20thus%20'%3CSPAN%3ElowBV%3C%2FSPAN%3E'%20(%3CSPAN%3Elow%20value%20building%3C%2FSPAN%3E)%20could%20refer%20to%20Boolean%20conditions%3C%2FP%3E%3CP%3E%3D%20(%20LU%20%26lt%3B%20V%2F2%20)%3C%2FP%3E%3CP%3Eor%2C%20in%20order%20to%20replace%20TRUE%20by%201.%3C%2FP%3E%3CP%3E%3D%20IF(%20LU%20%26lt%3B%20V%2F2%2C%201%20).%3C%2FP%3E%3CP%3EThis%20leads%20to%20the%20formula%3C%2FP%3E%3CP%3E%3D%20COUNT(%20lowBV%20)%3C%2FP%3E%3CP%3EConditional%20formats%20are%20a%20little%20more%20trouble%20because%20they%20do%20not%20accept%20arrays%20as%20the%20basis%20for%20formatting.%26nbsp%3B%20Using%20Dynamic%20Arrays%20I%20had%20to%20introduce%20a%20second%20cell-by-cell%20formula%20'highlight%3F'%3C%2FP%3E%3CP%3E%3D(%40LU%20%26lt%3B%20%40V%2F2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-377854%22%20slang%3D%22en-US%22%3ERe%3A%20Impossible%20formula%3F%20Possibly%20COUNTIF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-377854%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%20and%20it%20does%20not%20seem%20to%20produce%20the%20calculation%2C%20even%20though%20it%20is%20a%20valid%20formula.%20Maybe%20I%20am%20still%20doing%20something%20incorrectly%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-376569%22%20slang%3D%22en-US%22%3ERe%3A%20Impossible%20formula%3F%20Possibly%20COUNTIF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-376569%22%20slang%3D%22en-US%22%3EThe%20formula%20you%20need%20to%20return%20your%20desired%20result%20is%3A%3CBR%20%2F%3E%3DSUMPRODUCT(%3CBR%20%2F%3ECOUNTIF(U766%3AU6886%2C%3CBR%20%2F%3E%E2%80%9C%26lt%3B%E2%80%9C%26amp%3B%3CBR%20%2F%3EV766%3AV6886*0.50))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-376565%22%20slang%3D%22en-US%22%3ERe%3A%20Impossible%20formula%3F%20Possibly%20COUNTIF%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-376565%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F302984%22%20target%3D%22_blank%22%3E%40ZachStone9%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20the%20simplest%20is%20probably%20to%20set%20up%20a%20helper%20column.%20It%20has%20the%20advantage%20of%20giving%20you%20the%20number%20you%20are%20looking%20for%20while%20at%20the%20same%20time%20identify%20the%20properties%20that%20meet%20your%20criteria.%3C%2FP%3E%3CP%3EThere%20are%20a%20couple%20ways%20I%20could%20suggest%3A%3C%2FP%3E%3COL%3E%3CLI%3EAdd%20a%20column%20(D)%20that%20has%20the%20formula%20IF(B2%2FC2%26lt%3B%3D0.5%2C1%2C0)%20in%20the%20row%20D2%20(with%20LU%20values%20in%20starting%20in%20B2%20and%20V%20alues%20starting%20in%20C2%2C%20then%20in%20D1%3A%20%3DSUM(D2%3AD20)%20(this%20assumes%2020%20pairs%20of%20values).%3C%2FLI%3E%3CLI%3EAlternative%20add%20the%20column%20(E)%20with%20with%20the%20formula%20%3DB2%2FC2%20and%20copy%20it%20down%20until%20row%2020%20or%20to%20the%20end.%20in%20E1%20put%20the%20formula%20%3DCOUNTIF(E2%3AE20%2C%22%26lt%3B.5%22).%3C%2FLI%3E%3C%2FOL%3E%3CP%3EThen%20you%20can%20use%20conditional%20formating%20to%20highlight%20all%20the%20rows%20that%20are%20less%20than%20.5%3C%2FP%3E%3C%2FLINGO-BODY%3E
ZachStone9
New Contributor

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:

 

=COUNTIF(U766:U6886, V>(.5*U))


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.

5 Replies

@ZachStone9 

 

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:

  1. Add a column (D) that has the formula IF(B2/C2<=0.5,1,0) in the row D2 (with LU values in starting in B2 and V alues starting in C2, then in D1: =SUM(D2:D20) (this assumes 20 pairs of values).
  2. Alternative add the column (E) with with the formula =B2/C2 and copy it down until row 20 or to the end. in E1 put the formula =COUNTIF(E2:E20,"<.5").

Then you can use conditional formating to highlight all the rows that are less than .5

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

@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?

@ZachStone9 

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)

 

@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.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies