How to determine the frequency of an exact array's values?

Question

How to determine the frequency of an exact array's values?

JohnCTX 636

I am having some issues with finding out the frequency of an exact array's values

Examples: Result:
[3, 6, 8, 9, 10]
[3,4, 6, 8, 9]
[3, 6, 8, 9, 10]

Results: Count of exact array's values
[3, 6, 8, 9. 10] = 2
[3, 4, 6, 8, 9] = 1

So far, I have tried Frequency, CountIFS, and CountIF functions; received errors, or inaccurate results.

Users who have happened to know Excel's formulas should be able to solve this in reply.

Regards,

JohnCTX

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Lz._ 38,101 Reputation points

2021-01-14T07:21:11.327+00:00
@JohnCTX

[3, 6, 8, 9, 10], [3,4, 6, 8, 9]... do not represent arrays within Excel. To clarify your request could you upload a picture showing how these series of values are stored in your spreadsheet

Where/how you want to report their frequency (in a column next to each value? in a separate table?...)

Which version of Excel do you run?

Accepted answer

1 additional answer

Lz._ 38,101 Reputation points

2021-01-14T07:21:11.327+00:00

@JohnCTX

[3, 6, 8, 9, 10], [3,4, 6, 8, 9]... do not represent arrays within Excel. To clarify your request could you upload a picture showing how these series of values are stored in your spreadsheet

Where/how you want to report their frequency (in a column next to each value? in a separate table?...)

Which version of Excel do you run?

Answer 1

Emily Hua-MSFT 27,821

@JohnCTX

According to your sample, I suggest you try the formula =SUM(N(EXACT($A$1:$A$3,E1))), Exact function would determine the whether array values are same.

Any misunderstanding, please let me know.

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Lz._ 38,101 Reputation points

2021-01-14T09:47:13.187+00:00

@Emily Hua-MSFT . With your scenario =COUNTIF(A$1:A$3,E1) would be easier to understand and more efficient on a large range. Hope this makes sense. Cheers
JohnCTX 636 Reputation points

2021-01-14T15:00:45.937+00:00

Thank you. I should try that.

Regards,

JohnCTX

Answer 2

Ashish Mathur 99,575 Volunteer Moderator

Hi,

Enter this formula in cell C2

=GROUPBY(A2:A4,A2:A4,COUNTA)

Hope this helps.

User's image

Share via

How to determine the frequency of an exact array's values?

1 additional answer