Hi All
I am trying to use the count if function to start a process that will return the number of "wins" a name has in a table
I can then return this number as a percentage of the total entries for that name
I have fallen at the first as I cannot get countif to do as I want
Can anyone enlighten me on this Problem please
Moose
Try
G2=COUNTIFS(Table2[Name],F2)/COUNTA(Table2[Win/Place/lose])*100
Formula:
Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Hi Ankur
Thanks for the quick response
In the example Joe wins 4 times out of ten tries This is a strike rate percentage of 40%
Your solution returns 21%
Do I have to specify "win" in the formula you gave
Moose
Try
=COUNTIFS(Table2[Win/Place/lose],"win ",Table2[Name],F2)/COUNTIF(Table2[Name],F2)*100
Formula:Please Login or Register to view this content.
Note:- You have trailing space in B column for Win that is why i used the same criteria for that as "win ".
Last edited by shukla.ankur281190; 10-17-2015 at 03:37 AM.
Hi
That works great - I think
have added Tom and harry to table 2 and tom gets 100% for 1 win out of 1 - superb
Then I copied the formula to the next cell right to get the strike rate for places changed the name cell to "place" the reference cells back to F2 as they changed to G2
but I am getting Div/0 error and cannot see any reason why
any suggestions
moose
May be you have posted wrong file. Better if you can post the same working file with desired result. :
Oops what a dummy
saved the file on my machine forgot to upload it sorry
It's because Harry has had no wins. What do you want to appear when that is the case?
To simply show a blank cell if there have been no wins, edit the formula thus:
Please Login or Register to view this content.
Last edited by AliGW; 10-17-2015 at 03:22 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Good pointin answer to that question - an unhappy face ??? is that possible ?
but seriously the formula in cell H2 is not returning the percentage of places
Good point
but seriously the formula in cell H2 is not returning the percentage of places
That's because you have "Place " instead of "Place" in the formula (can you see the space after the 'e'?).
Sad face formula:
Please Login or Register to view this content.
Last edited by AliGW; 10-17-2015 at 03:34 AM.
Actually, all of your Win entries in the table have a trailing space, but the Place entries don't.
Let forget everything Try
G2=IFERROR(SUMPRODUCT((TRIM(Table2[Name])=TRIM($F2))*(TRIM(Table2[Win/Place/lose])="Win"))/SUMPRODUCT((TRIM(Table2[Name])=TRIM($F2))*1)*100,"")
Formula:Please Login or Register to view this content.
It will work if there will trailing space.
Last edited by shukla.ankur281190; 10-17-2015 at 03:38 AM.
Fine, but let's explain what we are doing and why!!!Let forget everything
@MadMoose: shukla.ankur281190's formula deals with the trailing spaces I mentioned, and gets rid of them if they are there before doing the calculation.
That would explain A great deal
Thank you for you help
I have got it going now and harry was a typing mismatch which returned the error result
if a name had no wins is it possible to put the smiley unhappy in
I can see the the previous function is for an error and thank you for that but what if it was a zero - this is just the devil in me
moose
For 0, you'll need to add another IF statement. I'm afraid i have to go out now, but i am sure that someone else will show you how. As for the smiley: I'm really not sure, but I doubt it. You could use conditional formatting with one of the icons available, though.
...at the risk of sowing further confusion and in order to account for trailing spaces should they be there or not then from AliGW's post #8 use the wildcard character. i.e.
=IFERROR(COUNTIFS(Table2[Win/Place/lose],"win*",Table2[Name],F2)/COUNTIF(Table2[Name],F2)*100,"")
And use CF for the non smiley as suggested.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thankyou one and all
I have now successfully incorporated this into my main work book and it works fine
just a note for anyone else with the same prob if you delete the *100 from the end of the function and set the cell to percentage you will get the percent sign in the cell after the figure
Once again thank you all
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks