Find if a specic date related values is bellow 100 and count as "1".

**Mr.Castle** · 03-12-2019, 10:03 PM

From C4:C100 i have a range of dates related to a range of numbers N4:N100. After puttin a specific date in G4 the formula must verify if N related values is bellow 100 then it shall be counted as "1" otherwise blank.

Example:

C4=10/02/2019 N4=554
C5=11/02/2019 N5=136
C6=12/02/2019 N6=4
C7=13/02/2019 N7=123
C8=14/02/2019 N8=90
C9=15/02/2019 N9=76
C10=16/02/2019 N10=151

G4=15/02/2019 H4=1

**FlameRetired** · 03-12-2019, 10:27 PM

You may have to change "," argument separators to ";" for your regional settings.

Please try

Formula:

Please Login or Register  to view this content.

**Mr.Castle** · 03-13-2019, 11:51 AM

The formula worked, .

If instead of <100 i wanted to count all numbers between 50-100,
Would it be necessary to change the formula?

Thank you

**GeoffW283** · 03-13-2019, 02:42 PM

Modifying Dave's formula in H4:

Formula:

Please Login or Register  to view this content.

Or, more succinctly if a little more obscurely:

Formula:

Please Login or Register  to view this content.

Find if a specic date related values is bellow 100 and count as "1".

LinkBack

Thread Tools

Rate This Thread

Display

Find if a specic date related values is bellow 100 and count as "1".

Re: Find if a specic date related values is bellow 100 and count as "1".

Re: Find if a specic date related values is bellow 100 and count as "1".

Re: Find if a specic date related values is bellow 100 and count as "1".

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Pivot Table - how to count the % of values = "4" and "5" from a column in the source data?

[SOLVED] Need addtional criteria to count where "Actual" dates earlier that "Scheduled" DATE

Using "index", "match", "min", "abs", to find the closest value in multiple criteria

[SOLVED] How to USE """"" cells count """"" change font color

Pivot - drag fields into Values area always comes out as "Count of", instead of "Sum of"

[SOLVED] Find "X" in range of cells and return various values based on the column "X" is found in.

return related cell values from "search Query"

Bookmarks

Bookmarks

Posting Permissions