question

JohnCTX-6479 avatar image
0 Votes"
JohnCTX-6479 asked JohnCTX-6479 commented

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

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



office-excel-itprooffice-vba-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@JohnCTX-6479

[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?

0 Votes 0 ·

1 Answer

emilyhua-msft avatar image
1 Vote"
emilyhua-msft answered JohnCTX-6479 commented

@JohnCTX-6479

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.

56561-capture26.jpg

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.


capture26.jpg (24.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@emilyhua-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

0 Votes 0 ·

Thank you. I should try that.

Regards,

JohnCTX

0 Votes 0 ·