Hey all:
Obviously something wrong, not sure what though?
Please Login or Register to view this content.
Just wanting to count the number of "Yes" I have that corresponds to the name found in column B (which is the same "Text" name found in B3) AND in the month in column D (which is equal to text, e.g. "May").
Any ideas?
That's not how you use COUNTIF. You don't use an equal sign. It's =COUNTIF(range, condition)
And, if you have multiple conditions, you use COUNTIFS. = COUNTIFS(range1, condition1, range2, condition2, ..., rangex, conditionx)
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Ah! Didn't realise I wasn't using COUNTIF...
I'm still stuck though? Are you able to help please?
Can you upload a sample sheet so we can cater to your needs?
Somethink like:
Formula:
Please Login or Register to view this content.
But, if you need that TEXT bit, I'd use SUMPRODUCT
Regards, TMS
Still not working?Please Login or Register to view this content.
B3 = name (Text)
'Overview - 2013'!$B$15:$B$9999 = name (Text)
C5 = May (Text)
'Overview - 2013'!$J$15:$J$9999 = "Yes" (Text)
Suggest you post a sample workbook with some typical data and mock up what you expect to see as the results.
Regards, TMS
This might work for you:
Formula:Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Yea that's spot on. How am I finding out total of "Yes" and "No"'s (i.e. Countif Yes and No)
Would you mind explaining the parts of the formula, briefly so that I can understand? I know it kinda breaks it down in excel with the hints, but I am not following the logic?
Which formula?
This one?Please Login or Register to view this content.
Name * Month * Yes + No? I can't understand this logic on how they're multiplying each other to give the answer?Please Login or Register to view this content.
I will attempt to make these formulae understandable (sometimes it is easier to know than to explain)
Note that both formulae have 3 sections. These 3 sections are virtually the same but done differently.
The top formula uses worksheet and cell references while the bottom one is generic using names or prompts as to what to fill in.
This part "('Overview - 2013'!$B$15:$B$9999=B3)" refers to the range B15:B9999 on the Overview worksheet and means that what the formula is going to use are all values in that range that are equal to the value of B3.
The bottom formula "(Name=Name)" is prompting you to replace "Name" with the range and what it is referring to.
This first part is then multiplied by the second part "(TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=C5)" or "(TEXT(Month,"mmmm")=Month)" This matches the values in D15:D9999 (these are dates) with the value in C5. The bottom formula is the generic version of what the top formula has. The multiplication of the first two parts of the formulae match up the values in column B that equal B3 with the appropriate values in column D that equal the MONTH and are appropriate for the values in column B.
The third part of the formulae "('Overview - 2013'!$J$15:$J$9999="Yes")" or "(Yes / No ="Yes")". Column J from J15:J9999 have values of either Yes or No. "('Overview - 2013'!$J$15:$J$9999="Yes")" identifies the values in column J that match Yes. "(Yes / No ="Yes")" is the generic version. The multiplication of this part of the formula with the first 2 identifies all the records that match ALL 3 conditions. This produces a count that the SUM part of the SUMPRODUCT then totals.
I hope that I haven't made this even more confusing. The Excel help files have some good examples of SUMPRODUCT and its uses.
If someone has something to add, amend, further explain, please do so.
Off the back of that question (COUNTIF), how would I do the following:
-Count both yes and no (avoid blanks)
-Count the blanks
-Count all Yes and No and Blank (Blank meaning there is a row there)
Just wanting to understand the logic so I can grasp how to do it myself
UPDATE: Just reading your response now!
Yea I understand, thanks.Originally Posted by newdoverman
I think I am probably asking the question wrongly or more importantly stupidly, as excel is trying to be as intuitive as possible, but I am just wanting to understand the way the COUNTIF segments of the formula work? As in "array" and then sometimes an "AND" or "OR" is used, and I am wanting to understand when I would need those two function (if at all!) in the example scenarios in my post number 13 (one before this) in terms of understanding when to apply this? Maybe I should probably invest some time in reading the manual in understand how they work in predefined formula's - where can I read or learn more?
Thank you kindly for your patience in explaining! Wish you were my teacher!
Think of it row by row. The formula has three (3) conditions, each of which can be be TRUE or FALSE. If any of the conditions is FALSE, the whole formula is FALSE and the row will not be included in the count. If all the conditions are TRUE, the whole formula is TRUE and the row will be counted. Multiplying TRUEs and FALSEs will give you a zero (0) or one (1) output.
So, you end up with a matrix of 0's and 1's which are added to give you a total count.
If you want to OR conditions, you have to use an Array Formula.
I asked once for a sample file; I'll ask again. Otherwise this is an interesting discussion that may have no useful end.
Regards, TMS
If you want the No values that match the other two criteria, change the Yes to No.
If you want a different month, or name, change the values in B3 or C5.
Think of COUNTIF as counting with 1 condition. COUNTIFS is similar to COUNTIF but allows several conditions to be applied....much like SUMPRODUCT allows several conditions.
When there is MORE than one condition consider COUNTIFS, SUMIFS, SUMPRODUCT for starters depending upon what you want to do. I don't know of a rule that you can apply in deciding what to use. Your experiences and preferences are probably your best guide.
Start off with real simple examples so that you can get a "feel" for what each function does before trying to use them in complicated situations. Even when you are comfortable with the functions, sometimes it pays to take one part of the function, in a formula, at a time to make sure that the parts work separately then put them together.
If you have specific questions about a formula or part of a formula feel free to ask.
@TMS
I like the row by row and FALSE idea.
@newdoverman: thanks, appreciate the feedback.
Thanks for that insight! Certainly explained a great deal there.
First off, this formula here by newdoverman is spot on for what I need:
What I am simply wanting is to look at column J and count up all the Yes + No total... I thought rather than ask for the answer, I wanted to try and figure it out myself, but I can't :PPlease Login or Register to view this content.
Any ideas? (Long winded to make a mock up spread-sheet, I would but I don't have time)
Me neitherLong winded to make a mock up spread-sheet, I would but I don't have time
Had enough time to replyPlease Login or Register to view this content.
All I am wanting is to count the Yes (which it's doing) and the No's.... can you not tell me how to do that much...
OK, I'm done here.
So am I:
Done it, that's it^Please Login or Register to view this content.
Wouldn't have thought it would be too complicated for a Guru...
Thanks newdoverman
Not too complicated. I just don't like offering solutions that I haven't been able to test. It can lead to inordinately long threads, not dissimilar to this one which could have been resolved much more quickly with a sample workbook attached to the first post. And, of course, adding riders to the original question.
But, whatever, you have your solution and you must have a feeling of satisfaction that you have resolved it yourself.
TMS
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
Sorry, you're right. I didn't mean to be rude and it is wrong of me to assume you know the answer.
I was under the impression I was so close to getting what I needed but I literally had no idea how to add the Yes and No's in the same column lol.
I understand where you are coming from. I will upload worksheets to make everyone elses life easier who is trying to help.
Thanks
If it's any consolation, the solution that you came up with is better than the "simple" solution I had in mind. Again, it works because you are doing math on a boolean result. Obviously, x="yes", or x="no", or x="blank/something else". So, if x="yes", or x="no", you'll get a 1. Neat
I won't labour the point, but often you can work up a solution which may look as though it should work but, when the OP tries it, it doesn't. If and when you get to see the data, it can be a lot different, in a lot of ways, than you expected.
But, good result, apology accepted. We're good
Please mark the thread solved.
TMS
Last thing I promise (on this thread at least!)
D have a tonne of dates in this custom format: "ddd - dd/mm/yy"Please Login or Register to view this content.
I will populate either £25 or £50 if there is a date input into column D.
C5 is a text: "May" (example)
I want to know the sum total of everything in I that corresponds to C5 (month) of column D...
I completely understand if you want to see an example
Different question, should be a new thread.
BUT ...
Formula:Please Login or Register to view this content.
Regards, TMS
Reality check. You can just get away with removing the >0
Formula:Please Login or Register to view this content.
Bit redundant really
Different question, should be a new thread.
BUT ...
Formula:Please Login or Register to view this content.
Regards, TMS
Hmm... getting #VALUE! popup...
That should work based upon the other logic I am using :s??
Column "I" is just formatted into GBP (£) currency. Just wanting the sum total of all that based upon date in D column...
Could have a #VALUE! or a non-numeric value in the column you are summing.
Formatting shouldn't make a difference. Content will.
Works OK for me ... yes, I did put it into a workbook, set up the worksheets, put some data in ...
It was the * before the last brackets that needed to be a comma that did itPlease Login or Register to view this content.
Mmm. It worked for me. And, as I said, you don't need the middle test, if you add a zero value, it's not going to make a huge difference. Unless you have negative values that you need to exclude.
I tend not to mix commas and asterisks. I tend to go for:rather than:Formula:Please Login or Register to view this content.but either way should work.Formula:Please Login or Register to view this content.
See post #29
Regards, TMS
That helps understand it a little easier. Thanks!
I've got it working, thanks
You're welcome. What was the problem?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks