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
COUNTIF Formula Help - Embed another formula
junebug in Excel on
1 Replies
COUNTIF help
brentonhobden in Excel on
3 Replies
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies
Excel Forumla to exclude empty cells.
ulken2019 in Excel on
6 Replies
Need FORMULA help please!!
marislav in Excel on
4 Replies