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

[Closed]
Report
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
-
 toony -
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

Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
2
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
Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
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