SOLVED

COUNTIFS with <>= and cell reference

%3CLINGO-SUB%20id%3D%22lingo-sub-1318803%22%20slang%3D%22en-US%22%3ECOUNTIFS%20with%20%26lt%3B%26gt%3B%3D%20and%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318803%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20my%20issue%20is%20that%20I%20am%20trying%20to%20count%20the%20number%20of%20observations%20between%20a%20range%20of%20values%2C%20and%20the%20COUNTIFS%20formula%20fits%20perfectly.%20If%20I%20write%20the%20formula%20as%20%26gt%3B%3D%2C%20%26lt%3B%3D%20and%20write%20the%20number%20it%20works%20fine%2C%20however%2C%20if%20I%20write%20%26gt%3B%3D%2C%20%26lt%3B%3D%20and%20reference%20a%20cell%20with%20the%20same%20number%2C%20it%20doesn%C2%B4t%20work%2C%20the%20result%20shows%200.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20problem%20because%20I%20pretend%20to%20apply%20the%20formula%20to%20a%20big%20set%20of%20value%20ranges%2C%20and%20the%20fact%20that%20I%20have%20to%20write%20de%20number%20in%20each%20COUNTIFS%20makes%20it%20impossible%20to%20write%20once%20and%20drag%20the%20formula%20to%20collect%20the%20entire%20set%20of%20ranges.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1318803%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1318900%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20with%20%26lt%3B%26gt%3B%3D%20and%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1318900%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F627727%22%20target%3D%22_blank%22%3E%40Carlos1290%3C%2FA%3E%26nbsp%3BTry%20writing%20the%20criteria%20similar%20to%20this%20(suppose%20A1%20holds%20the%20number%20you%20want%20to%20use%20in%20the%20COUNTIFS%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%22%26lt%3B%3D%22%20%26amp%3B%20A1%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1319829%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20with%20%26lt%3B%26gt%3B%3D%20and%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1319829%22%20slang%3D%22en-US%22%3E%3CP%3EThak%20you%20very%20much%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20That%20works%20perfectly!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1319857%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20with%20%26lt%3B%26gt%3B%3D%20and%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1319857%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F627727%22%20target%3D%22_blank%22%3E%40Carlos1290%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20nice%20feature%20of%20%3CSTRONG%3ECOUNTIFS%3C%2FSTRONG%3E%20%3CEM%3Eetc%3C%2FEM%3E%2C%20is%20that%20it%20is%20possible%20to%20test%20many%20intervals%20simultaneously%20as%20an%20array%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20COUNTIFS(%20%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3Eobserved.times%2C%20%3C%2FFONT%3E%3C%2FSTRONG%3E%3CSTRONG%3E%3CFONT%3E%22%26gt%3B%3D%22%20%26amp%3B%20interval.start%2C%3C%2FFONT%3E%3C%2FSTRONG%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3Eobserved.times%2C%20%22%26lt%3B%22%26nbsp%3B%20%26amp%3B%26nbsp%3B%20interval.end%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3EIf%20the%20results%20form%20a%20continuous%20set%20of%20'bins'%2C%20%3CSTRONG%3EFREQUENCY%3C%2FSTRONG%3E%20will%20achieve%20a%20similar%20result%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20FREQUENCY(%20observed.times%2C%20time.slots%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1322531%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFS%20with%20%26lt%3B%26gt%3B%3D%20and%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1322531%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3BI%20am%20just%20learning%20to%20use%20it!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello, my issue is that I am trying to count the number of observations between a range of values, and the COUNTIFS formula fits perfectly. If I write the formula as >=, <= and write the number it works fine, however, if I write >=, <= and reference a cell with the same number, it doesn´t work, the result shows 0.

 

This is a problem because I pretend to apply the formula to a big set of value ranges, and the fact that I have to write de number in each COUNTIFS makes it impossible to write once and drag the formula to collect the entire set of ranges.

4 Replies
Highlighted
Best Response confirmed by Carlos1290 (New Contributor)
Solution

@Carlos1290 Try writing the criteria similar to this (suppose A1 holds the number you want to use in the COUNTIFS:

"<=" & A1

 

Highlighted

Thak you very much @Riny_van_Eekelen  That works perfectly!

Highlighted

@Carlos1290 

A nice feature of COUNTIFS etc, is that it is possible to test many intervals simultaneously as an array

= COUNTIFS(

observed.times, ">=" & interval.start, 

observed.times, "<"  &  interval.end )

If the results form a continuous set of 'bins', FREQUENCY will achieve a similar result

= FREQUENCY( observed.times, time.slots )

Highlighted

Thank you @Peter Bartholomew I am just learning to use it!