=COUNTIFS(RANGE;X)

%3CLINGO-SUB%20id%3D%22lingo-sub-1934714%22%20slang%3D%22en-US%22%3E%3DCOUNTIFS(RANGE%3BX)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1934714%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Emy%20question%26nbsp%3B%20is%20if%20I%20have%20to%20put%20into%20the%20X%20criteria%20a%20relalatives%20how%20can%20I%20do%20it%3F%3C%2FP%3E%3CP%3EFor%20instance%3A%3C%2FP%3E%3CP%3E-%20E1%20cell%20contains%20%224%22%3C%2FP%3E%3CP%3E-%20The%20range%20is%20a%20numeric%20order%20from%26nbsp%3B1%20upto%20100%20in%20the%20cells%20between%20A1%20and%20A100.%3C%2FP%3E%3CP%3E-%20And%20the%20COUNTIFS%20function%20has%20to%20return%20with%20the%20count%20of%20numbers%20that%20are%26nbsp%3Bless%20than%20E1%20cell's%20containt.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20solution%20should%20be%26nbsp%3B3%20(1%2C%202%20and%203)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20the%20formular%20looks%20out%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1934714%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1934769%22%20slang%3D%22en-US%22%3ERe%3A%20%3DCOUNTIFS(RANGE%3BX)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1934769%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F882869%22%20target%3D%22_blank%22%3E%40Manner20%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20good%20explanation%20of%20the%20%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-countifs-function%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Efunction%3C%2FA%3E.%20Basically%20the%20function%20is%20broken%20by%20range%20then%20criteria.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20case%2C%20to%20get%20all%20values%20that%20are%20less%20than%2C%20you%20have%20to%20tie%20the%20strings%20together%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA1%3AA100%20(the%20range)%3C%2FP%3E%3CP%3EE1%20%3D%204%20(the%20criteria)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS(%24A%241%3A%24A%24100%2C%22%26lt%3B%22%26amp%3BE1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1934817%22%20slang%3D%22en-US%22%3ERe%3A%20%3DCOUNTIFS(RANGE%3BX)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1934817%22%20slang%3D%22en-US%22%3EHi%20Mannar20%3CBR%20%2F%3E%3CBR%20%2F%3EYou%20can%20use%20this%20formalue%3CBR%20%2F%3E%3DCOUNTIF(A1%3AA100%2C%22%26lt%3B%22%26amp%3BE1)%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi,

my question  is if I have to put into the X criteria a relalatives how can I do it?

For instance:

- E1 cell contains "4"

- The range is a numeric order from 1 upto 100 in the cells between A1 and A100.

- And the COUNTIFS function has to return with the count of numbers that are less than E1 cell's containt.

 

The solution should be 3 (1, 2 and 3)

 

How the formular looks out?

2 Replies

@Manner20 

Here's a good explanation of the function. Basically the function is broken by range then criteria. 

 

In your case, to get all values that are less than, you have to tie the strings together

 

A1:A100 (the range)

E1 = 4 (the criteria)

 

=COUNTIFS($A$1:$A$100,"<"&E1)

 

Hi Mannar20

You can use this formalue
=COUNTIF(A1:A100,"<"&E1)