On column V i would like to count how many talke to agents calls i had. that means i have to count if only positive information from column i.
But still some days it happens i only have zero informations so my count if will return zero value. I would like that my countifs to return blank in this case.
Thank you very much in advance!
Countif.xlsx
The formula in column S should be:
Please Login or Register to view this content.
Last edited by popipipo; 10-13-2022 at 01:38 PM.
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
The formula is =COUNTIFS(S:S,U2,I:I,">0") but if column i has 0 it returns 0. I think you did`t read my post.
If I did't read your post I could not give you a answer.
Did you try my formula?
Sorry I meant this:
The formula in column S should be:
Last edited by popipipo; 10-13-2022 at 01:40 PM.
O nice information But still i need my case solved would you help me ?) thank you a lot!
I think with this formula the problem is solved.
I want to avoid 0 as a result can you help me with this? if you put 00:00 in each row of column I the result will be zero so...
is this what you are looking for in V2?
=IF(COUNTIFS(S:S,U2,I:I,">0")=0,"",COUNTIFS(S:S,U2,I:I,">0"))
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
You could use a number format like 0;-0;;@ to suppress displaying 0. Or use trickery like
Formula:
Please Login or Register to view this content.
Explaining the trickery. For 1/(1/x), if x is 0, then 1/0 returns the error value #DIV/0!, and 1/(#DIV/0!) also returns #DIV/0!. On the other hand, if x is any number other than 0, 1/x is its reciprocal, so also a number; 1/(1/x) is the reciprocal of 1/x, so x itself; that is, 1/(1/x) is just x when x isn't 0. IFERROR(a,b) returns a when it's not an error value, or b when a is an error value, so IFERROR(1/(1/x),b) returns x when x isn't 0 because 1/(1/x) is just x, or b when x is 0 because when x is zero, 1/(1/x) is the #DIV/0! error value.
Last edited by hrlngrv; 10-15-2022 at 03:50 AM. Reason: added explanation
Sam Caprici Could you explain me a little bit how this formula works. thank you guys!
Last edited by alexxl; 10-14-2022 at 10:09 AM.
The countif formula I gave you is simply incorporating an IF statement into it, saying the following.
first, count items in col S that match what is in cell U2 and items in col I that are greater than 0.
second, the IF statement is saying that if there are no items meeting both criteria in the countif statement, in other words, there are none or 0, since you don't want a 0 returned, make the outcome be blank, but if there are, then return the count of those items (the second countif statement).
hope that helps.
Last edited by Sam Capricci; 10-14-2022 at 11:05 AM.
I gave you an explanation in post #11. AND thanks for the rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks