[Solved] Counting Occurrences of Multiple Precise criteria

[BlackRose]

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.

[RusselB]

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

Code: Select all

=COUNTIF(A2:A500;"25")+COUNTIF(A2:A500;"31")+COUNTIF(A2:A500;"48")

will work.
Alternatively you could use the (undocumented) COUNTIFS function.

Code: Select all

=countifs(a2:a500;"25";a2:a500;"31";A2:A500;"48")

I'm guessing you accidentally left out the closing quotes after the 48 in your posted formula

[BlackRose]

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.

[Sliderule]

Please see the attached file . . . a Calc spreadsheet using the DCOUNT function.

For an explanation, review either:

1. https://wiki.openoffice.org/wiki/Docume ... T_function
2. Calc Help: DCOUNT

In the Calc file below,

1. The defined Calc column containing the text strings is in A1:A101 ( you may extend this to any number of rows )
2. The DCOUNT formula is in C2 ( this is the result you are asking for )
   

   Code: Select all

   =DCOUNT(A1:A101; 0; F1:F101)

3. The Criteria to count is F1:F101 ( you may extend this to any number of rows )

Any of the above parameters of course, can be changed to meet your specific needs.

DCOUNT_20180422.ods

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.

[BlackRose]

Please see the attached file . . . a Calc spreadsheet using the DCOUNT function.

For an explanation, review either:

1. https://wiki.openoffice.org/wiki/Docume ... T_function
2. Calc Help: DCOUNT

In the Calc file below,

1. The defined Calc column containing the text strings is in A1:A101 ( you may extend this to any number of rows )
2. The DCOUNT formula is in C2 ( this is the result you are asking for )
   

   Code: Select all

   =DCOUNT(A1:A101; 0; F1:F101)

3. The Criteria to count is F1:F101 ( you may extend this to any number of rows )

Any of the above parameters of course, can be changed to meet your specific needs.

DCOUNT_20180422.ods

I hope this helps, please be sure to let me / us know.

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.

[MrProgrammer]

Hi, and welcome to the forum.

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.

=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.

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.

[Lupp]

(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.)

[Villeroy]

filtered_pilot.ods

yet another pivot table

(11.77 KiB) Downloaded 169 times

[Lupp]

Who did set this thread SOLVED based on what answer?

[robleyd]

It seems OQ edited the first post once shortly after the fifth post (fourth response) in this topic was made.