[Solved] Counting Occurrences of Multiple Precise criteria
[Solved] Counting Occurrences of Multiple Precise criteria
I have several large strings of numbers entered as text. I'm having trouble getting a count of how many times a list of certain numbers occur.
Example: Say the list of numbers in a string ranges from 0 to 999. I want to find the number of occurrences for 25,31,and 48.
The problem I encounter is when I use =SUMPRODUCT(COUNTIF(A2:A500;{"25";"31";"48})) the number I get counts not only occurrences of 25,31, and 48; but it
also counts 125,131,148, 225,231, 248, ect. I would greatly appreciate if anyone can tell me a formula that will return just the occurrences of 25,31,and 48.
One more little hick up. I can't change the numbers from text entries because 015 is not the same thing as 15.
Example: Say the list of numbers in a string ranges from 0 to 999. I want to find the number of occurrences for 25,31,and 48.
The problem I encounter is when I use =SUMPRODUCT(COUNTIF(A2:A500;{"25";"31";"48})) the number I get counts not only occurrences of 25,31, and 48; but it
also counts 125,131,148, 225,231, 248, ect. I would greatly appreciate if anyone can tell me a formula that will return just the occurrences of 25,31,and 48.
One more little hick up. I can't change the numbers from text entries because 015 is not the same thing as 15.
Last edited by BlackRose on Sun Apr 22, 2018 10:31 pm, edited 1 time in total.
Because hair belongs on the head, not in the hands.
OpenOffice 4.1.5 on Windows 10
OpenOffice 4.1.5 on Windows 10
Re: Counting Occurrences of Multiple Precise criteria
Why are you using the SUMPRODUCT? Did you try just using the COUNTIF function?
Since you are only wanting the counts for the 3 occurrences, you could easily just use the COUNTIF for each ocurrence, then add the results together.
If you only want one formula, then will work.
Alternatively you could use the (undocumented) COUNTIFS function.
I'm guessing you accidentally left out the closing quotes after the 48 in your posted formula
Since you are only wanting the counts for the 3 occurrences, you could easily just use the COUNTIF for each ocurrence, then add the results together.
If you only want one formula, then
Code: Select all
=COUNTIF(A2:A500;"25")+COUNTIF(A2:A500;"31")+COUNTIF(A2:A500;"48")
Alternatively you could use the (undocumented) COUNTIFS function.
Code: Select all
=countifs(a2:a500;"25";a2:a500;"31";A2:A500;"48")
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Re: Counting Occurrences of Multiple Precise criteria
RusselB wrote:Why are you using the SUMPRODUCT? Did you try just using the COUNTIF function?
I used the SUMPRODUCT because It allows you to set multiple OR criteria for COUNTIF without adding multiple COUNTIF functions. Using COUNTIFS makes the criteria AND instead of OR. I did try using COUNTIF alone to be sure and the same problem exists. The problem lies in the fact that functions, or at least the functions I'm familiar with, in Calc default to criteria that is a partial match for a cell being counted as a match. Thank you for being the 1st to try and answer my question though.
Because hair belongs on the head, not in the hands.
OpenOffice 4.1.5 on Windows 10
OpenOffice 4.1.5 on Windows 10
Re: Counting Occurrences of Multiple Precise criteria
Please see the attached file . . . a Calc spreadsheet using the DCOUNT function.
For an explanation, review either:
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
For an explanation, review either:
- https://wiki.openoffice.org/wiki/Docume ... T_function
- Calc Help: DCOUNT
- The defined Calc column containing the text strings is in A1:A101 ( you may extend this to any number of rows )
- The DCOUNT formula is in C2 ( this is the result you are asking for )
Code: Select all
=DCOUNT(A1:A101; 0; F1:F101)
- The Criteria to count is F1:F101 ( you may extend this to any number of rows )
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Counting Occurrences of Multiple Precise criteria
Thank you this is very helpful, but though I'm not sure the truth is I may have made a mistake yesterday. After working for over 12 hours on this project I may have been seeing errors were none existed. If that is the case I'm sorry for having wasted your time, but I did learn something new that can help me on other aspects of this project.Sliderule wrote:Please see the attached file . . . a Calc spreadsheet using the DCOUNT function.
For an explanation, review either:In the Calc file below,
- https://wiki.openoffice.org/wiki/Docume ... T_function
- Calc Help: DCOUNT
Any of the above parameters of course, can be changed to meet your specific needs.
- The defined Calc column containing the text strings is in A1:A101 ( you may extend this to any number of rows )
- The DCOUNT formula is in C2 ( this is the result you are asking for )
Code: Select all
=DCOUNT(A1:A101; 0; F1:F101)
- The Criteria to count is F1:F101 ( you may extend this to any number of rows )
I hope this helps, please be sure to let me / us know.
Because hair belongs on the head, not in the hands.
OpenOffice 4.1.5 on Windows 10
OpenOffice 4.1.5 on Windows 10
- MrProgrammer
- Moderator
- Posts: 4906
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Counting Occurrences of Multiple Precise criteria
Hi, and welcome to the forum.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
=SUMPRODUCT(A2:A500={"25";"31";"48"}) based on my interpretation of the initial post that each cell in the range A2:A500 contains one value stored as text.BlackRose wrote:The problem I encounter is when I use =SUMPRODUCT(COUNTIF(A2:A500;{"25";"31";"48})) the number I get counts not only occurrences of 25,31, and 48; but it also counts 125,131,148, 225,231, 248, ect.
If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Last edited by MrProgrammer on Mon Apr 23, 2018 9:55 pm, edited 1 time in total.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Counting Occurrences of Multiple Precise criteria
(Once again this site logged me out unnoticed, and my thoroughly composed answer was lost when I tried to post it.
I'm not in the mood to do everything again.)
Just two remarks:
-1- The missing doublequote was already mentioned by RusselB in his first reply.
-2- The actual intentions of the OQ BlackRose were not at all clearly expressed. In specific I could not get sure about whether the tally should be increadsed BY ONE for each LIST containing ONE of the numbers, for EACH number occurring AT LEAST once in a specific list, or by the NUMBER OF OCCURRENCES of any of the chosen numbers within each one of the lists.
The OQ should really have made this clear with words AND with thoroughly chosen examples.
Concerning the most likely case that the total of all the occurrences of the chosen numbers over all the lists is required, I cannot think of a half way clear solution without at least three helper columns.
See attachment. (Tested with AOO 4.1.5.)
The first formula, most closely following the approach by BlackRose can only work if the option 'Search criteriy ... ... must apply to whole cells.' is NOT enabled. (A bad setting, imo.)
I'm not in the mood to do everything again.)
Just two remarks:
-1- The missing doublequote was already mentioned by RusselB in his first reply.
-2- The actual intentions of the OQ BlackRose were not at all clearly expressed. In specific I could not get sure about whether the tally should be increadsed BY ONE for each LIST containing ONE of the numbers, for EACH number occurring AT LEAST once in a specific list, or by the NUMBER OF OCCURRENCES of any of the chosen numbers within each one of the lists.
The OQ should really have made this clear with words AND with thoroughly chosen examples.
Concerning the most likely case that the total of all the occurrences of the chosen numbers over all the lists is required, I cannot think of a half way clear solution without at least three helper columns.
See attachment. (Tested with AOO 4.1.5.)
The first formula, most closely following the approach by BlackRose can only work if the option 'Search criteriy ... ... must apply to whole cells.' is NOT enabled. (A bad setting, imo.)
- Attachments
-
- aoo93318SpecialCounting_1.ods
- (89.12 KiB) Downloaded 92 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Counting Occurrences of Multiple Precise criteria
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Counting Occurrences of Multiple Precise criter
Who did set this thread SOLVED based on what answer?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: [Solved] Counting Occurrences of Multiple Precise criter
It seems OQ edited the first post once shortly after the fifth post (fourth response) in this topic was made.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers