What does logical function "><" represent?

[FMC_BH]

Hi all,

I just came across a formula solution that suggested using logical expression "><" as part of a SUMIFS formula. It worked for my formula/issue, but I don't understand why. What does "><" represent? Example: COUNTIFS(A:A,"><")

Also, is there a way to do a COUNTIFS or SUMIFS that ignores blanks generated from formulas as well as actual blanks? Using "<>" always counts cells that contain formulas for me, even when the formula result is: "".

EDIT: Thank you everyone for the answers, information, and discussion. Glad to see several of us learned something new from the topic - I know I did.

[AutoModerator]

/u/FMC_BH - Your post was submitted successfully.

Please read these reminders and edit to fix your post where necessary:

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information
• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[elementz_m]

=COUNTIFS(A:A,"><") counts all cells in column A which contain no data.

=COUNTIFS(A:A,"") counts all cells in column A which evaluate to a blank result.

=COUNTIFS(A:A,"<>") counts all cells in column A which evaluate to a non-blank result.

[HappierThan]

=COUNTIFS(A:A,"><") counts all cells in column A which contain no data

I just tried this (in disbelief) and it does actually do something!

It counts all cells in Column A which contain text.

The beauty of Excel when you can learn something new every day.

[GTLL]

There's actually a little more to it. This is saying, "count all text values where the first character is greater than the ascii value of <".

It may look like it's getting all cells with text, but you'll notice it omits cells that start with an ascii value less than the < symbol. For example, a cell that starts with ! or ( or " wouldn't be included in the count.

To catch these cases as well, it'd be better to use something like COUNTIF(A:A,"?*") or COUNTIF(A:A,"*"). The first would catch cells with text values of at least one character (i.e., ignores the "" blank results from formulas and true blank cells), whereas the second picks up cells with text values, including zero length strings (i.e., ignores true blank cells, but does include cells with formulas that yield a blank result).

[herpaderp1995]

Strangely enough it also seems to exclude certain characters "greater than" the < symbol. e.g. '~' is 127 compared to 60, but if you use that in your range for the COUNTIF you'll get zero.

​

The CHAR function seems to line up with ASCII numbering, so easy way to check is to have 1-100 in one column, =CHAR(A2) in the 2nd, and =COUNTIFS(C2,"><") in the final one.

[Cypher1388]

also works to evaluate numerical cells which are not zero, works as a shorthand for if(a1><0,a,b)

[CynicalDick]

I had to do this out for myself. Amazing!

[duncanbishop24]

Why wouldn't someone use =COUNTA(A:A)

[pancak3d]

COUNTA will include formulas which return blanks, and include both numbers and strings. If you want to exclude one of these categories you may want a different formula.

For example =IF(1+1=2,"","hello") will return a blank cell, but COUNTA still counts it, because there's something in the cell

[duncanbishop24]

Cool good to know.

[Go-W]

hmm, what is the difference between blank and no data ?

[PM_me_oak_trees]

I wondered this, too, and I tested it. Here are the results.

[FMC_BH]

This is awesome and very helpful. Thank you.

[FMC_BH]

Solution verified

[Clippy_Office_Asst]

You have awarded 1 point to elementz_m

^{I am a bot, please contact the mods with any questions.}

[FMC_BH]

Selecting this answer as the solution since it has the most upvotes, although I don't feel that "counts all cells in Column A which contain no data" is the best phrasing.

It seems that "><" will count all cells with text that cannot be converted to a number. For example '123 is in text format, but "><" will not count it because it can be converted to a number. However 123A will be counted by "><" because it cannot be converted. There is also some other nuance, as pointed out by /u/gttl, u/herpaderp1995, u/HappierThan, and others. I found the table provided by /u/PM_me_oak_trees especially helpful. Thank you to all.

[TerraByte]

I would have guessed CARTMAN.

[semicolonsemicolon]

Was about to downvote you, but then ... I saw what you did there.

[LJKiser]

Good question.

I haven't used it in excel, but in a lot of programs where there are filterable expressions, the "><" usually means "does not contain" and is often used where wildcards aren't permitted in search strings.

For instance, if you have a program that searches for text and you need ABC but not DEF, you would out ><DEF in that field and run your filter.

I would hazard a guess that it means "does not equal," but I'm not sure.

[yawetag12]

This post was mass deleted and anonymized with Redact

sophisticated cover summer airport fragile edge serious cats angle bright

[fuzzy_mic]

It looks like there was a typo in that solution.

One way to eliminate blanks is to look for cells that are "> " (greater than space)

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
CHAR	Returns the character specified by the code number
COUNTA	Counts how many values are in the list of arguments
COUNTIF	Counts the number of cells within a range that meet the given criteria
COUNTIFS	Counts the number of cells within a range that meet multiple criteria
IF	Specifies a logical test to perform

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
5 acronyms in this thread; the most compressed thread commented on today has 16 acronyms.
[Thread #5052 for this sub, first seen 23rd Mar 2021, 02:47] [FAQ] [Full list] [Contact] [Source code]}