,">"&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),"")

4 replies

Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
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)
About COUNTIFS

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.
3
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.
2
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
2
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
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
1