- 511K Members
- 6,399 Online
- 608K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Impossible formula? Possibly COUNTIF Function

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2019 04:42 PM

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.

Labels:

5 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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:

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-18-2019 07:50 PM

The formula you need to return your desired result is:

=SUMPRODUCT(

COUNTIF(U766:U6886,

“<“&

V766:V6886*0.50))

=SUMPRODUCT(

COUNTIF(U766:U6886,

“<“&

V766:V6886*0.50))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2019 11:36 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2019 07:40 PM

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
01-16-2019
181
Views

0 Likes

1 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft