# ,">"&0),"") at the end of a countif formula

Closed
jutulu Posts 34 Registration date Monday March 10, 2014 Status Member Last seen September 21, 2015 - Mar 16, 2014 at 09:22 PM
toony - Mar 19, 2014 at 03:46 PM
Hello,

I have the below formula, but don't understand why the last part of it ,">"&0),""). Can somebody clarify pls?

=IF(COUNTIF(\$AJ\$2:\$AJ9,\$AJ9)=1,COUNTIFS(\$AJ\$2:\$AJ11323,\$AJ9,\$AQ\$2:\$AQ11323,">"&0),"")
Related:

## 4 responses

Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Mar 16, 2014 at 10:10 PM
Jutulu, Good evening.

It's a Classic formula of IF...>>Then...>>Else

IF Condition
.
....Then 1st Action
.
....Else 2nd Action

Look...
1)
=IF COUNTIF(\$AJ\$2:\$AJ9,\$AJ9) = 1
.
... , COUNTIFS(\$AJ\$2:\$AJ11323,\$AJ9,\$AQ\$2:\$AQ11323,">"&0)
.
... , "" )

If Condition is TRUE
....then Execute Countifs
....else null

2)

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...)

COUNTIFS(\$AJ\$2:\$AJ11323,\$AJ9,\$AQ\$2:\$AQ11323,">"&0)
.....criteria_range1, criteria1,
.....\$AJ\$2:\$AJ11323,\$AJ9,
.
[criteria_range2, criteria2]
\$AQ\$2:\$AQ11323,">"&0

Obs.: ">0" and ">"&0 works in the same way.

Is it what you want?
I hope it helps.
jutulu Posts 34 Registration date Monday March 10, 2014 Status Member Last seen September 21, 2015 2
Mar 16, 2014 at 10:42 PM
Many thanks Belo,

I understand the formula, more yet after your clear explanation. However, the part I didn't get is; why ,">"&0. I read it as bigger than cero, but I suppose it stands for something else. Apologies, but I just want to understand this part of it. It is not clear and working with it everyday. I just think that if I understand it, surely is going to be useful to me. Many thanks

Obs.: ">0" and ">"&0 works in the same way.
Many thanks for taking the time to explain it so step by step. I've read it several times after work and I think that I get it. Thank you again
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Mar 17, 2014 at 07:40 AM
jutulu, Good morning.

"...Obs.: ">0" and ">"&0 works in the same way...."

The only time that this syntax is mandatory, is the condition when we want this function to be a direct comparison to a reference cell .

In this case the symbol "&" serves to put the pieces together as if they were a Plain Text.

">01/03/2014" means that only dates greater than 01/03/2014 will be selected. But this argument is FIXED within the formula .
If you have this same date in a separate cell, for example, D4 , the expression should be written like this: ">" & D4 .

If you put ">D4" , the formula will search the cell contents as plain text >D4 .
But , as you want a "GREATER THAN" condition and the contents of cell D4, in this case 01/03/2014 , the & symbol makes Excel understands: > and 01/03/2014. That is >01/03/2014 .

Now, if you wonder why even without need to use this trick, the person who wrote the formula used it to search for values?? greater than zero, instead to use simply ">0", I imagine it's just a matter of personal choice . Style maybe. I don't know.

I hope it helps.
Have a nice day.
--
Belo Horizonte, Brasil.
Marcílio Lobão