Excel Tutorial: Count the number of occurrences that only occur once, this formula is very NICE

Tanabata romantic date "Hui"

Super Member Lifetime Card 23% off for a limited time

The original price is 168. Sign up today for only 129!

Don't miss it. ヾ(◍°∇°◍)ﾉﾞ

In the last issue, I wrote an article on the application of how to count uniquely. In this issue, there is a new problem, which requires the number of products that only appear once in the statistical data.

Issues in this issue:

The yellow marks in the figure are for the convenience of everyone to understand the problem.

To know the number of times the product name in the above figure has appeared in the data, we can get it by conditional counting. By entering the formula =COUNTIF($A$3:$A$22,A3) in cell B3 of column B

When using the conditional counting function formula here, we should pay attention to one thing, make an absolute reference to the conditional area, because here we want to get the number of times the product appears in the entire data area, so the conditional area here is a fixed area to avoid drop-down When dragging the formula, the condition area changes, resulting in wrong results.

Through the auxiliary data in column B, we can see that there are 5 products that appear only once. To get the sum of these five 1s, we can get the result through the conditional sum function.

Enter the formula =SUMIF(B3:B22,1) in the target cell

When using the SUMIF function, if the condition area and the summation area are the same area, we can omit the parameter of the summation area.

When the article sees this, it is estimated that some readers will ask whether it is possible to get the result directly in one step without adding auxiliary columns. Of course, it is also possible. It also uses the function SUMPRODUCT used in the previous article. This function has many unofficial usages. , when there was no multi-conditional summation function SUMIFS a long time ago, this function can get the result of multi-conditional summation. In this question, by combining SUMPRODUCT+ COUNTIF, the result can be obtained directly in one step.

Formula=SUMPRODUCT((COUNTIF(A3:A22,A3:A22)=1)*1)

This function is equivalent to multiplying the judgment result with a count of 1 by 1 to obtain a sum of products.

The above string of TRUE and FALSE characters is selected by pressing the F9 key when COUNTIF(A3:A22,A3:A22)=1 in the formula.

Function of F9 in Excel: When editing formulas, to view the results returned by some formulas, you can select them first, then press the F9 key, and press Ctrl+Z to return the formulas.

There may be more than one solution to the problem in the article, and readers may also have better methods. You are welcome to leave a message for discussion, and put forward your own suggestions and methods. If you have suggestions or do not understand the article, you are also welcome to leave a message in the message area at the end of the article.

That's all for today's sharing. If the tutorial is useful to you, I hope you will share and support the editor! Every like and repost of yours is the motivation to support the editor to insist on originality.

Why choose us, the platform advantage is there

Advantage ①: VIP can learn all tutorials, unlimited learning + supporting material source files + unlimited downloads

Advantage 2: Support WeChat public account + applet + APP + PC website for multi-platform learning

Advantage ③: Support offline viewing, double-speed viewing, allowing you to learn anytime, anywhere

Advantage ④: VIP logo, distinguished status + independent after-sales group answers

★ FAQ ★

①Xiao Ai: Can you watch all the courses after buying VIP ?

Well, yes, including future updates (thanks for your support, we will insist on high-quality teaching updates)

②Xiao Ai: Hello, how do you study ?

Pay attention to the Aizhiqu Education service account, you can directly enter the official website to learn from the menu navigation, or you can enter the applet and download the APP!

Computer login to https://www.92zhiqu.com

(Mobile phone, computer learning is essential)

③Xiao Ai: what to do if you do n’t understand

Provide after-sales answers, pay to contact customer service and join the group (pay to contact WeChat customer service, just take a screenshot)

④Xiao Ai: Provide video courseware materials ?

Well, original high-definition video, these are provided (original tutorial, these are the most basic requirements)

⑤Classmate Xiao Ai: Validity period of study ?

Annual membership and lifetime membership, unlimited lifetime membership (self-built website + original tutorial + Aichiqu brand guarantee)

⑥ Primary school students: Does the website support accelerated viewing ?

kindness. At present, mobile phone applet + APP + computer PC all support 1.5 times accelerated viewing or slow playback

⑦ Does the course support offline download ?

kindness. Both Android and Apple IOS APP support mobile phone offline download

Computer downloads are provided by contacting WeChat customer service 18074746783.

⑧ How about the quality of the course ?

Aizhiqu Education high-quality video as the starting point, high-definition subtitles, support for audition

If there is anything else you need to consult, please contact WeChat customer service 18074746783

Long press to identify the QR code "Add Customer Service"

Click to read the original text , log in to the official website with one click, and learn from a large number of video VIPs! (you can try it out)