,">"&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
toony - Mar 19, 2014 at 03:46 PM
Related:
- Xbox-2309-1001-3-0
- //192.168.l.0 - Guide
- Xbox App - Download - Digital stores
- Whatsapp-desktop-2-2401-5-0.msixbundle - Download - Instant messaging
- Fsutil behavior set disableencryption 0 - Guide
- *#0*# huawei - Guide
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
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.
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.
jutulu
Posts
34
Registration date
Monday March 10, 2014
Status
Member
Last seen
September 21, 2015
2
Mar 16, 2014 at 10:42 PM
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.
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
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
"...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
