Tips: To find exactly articles with useful content for readers, search on Google with the syntax: "Keyword" + "khoafastnews". (Example: new card for new priest Khoafastnews).Search now
33 lượt xem

Excel COUNTIF and COUNTIFS in two bodies however however OR conditions attached to – KHOA

thes tutorial explains how to let emotions Excel’s COUNTIF and COUNTIFS all functions and to be seen cells in two bodies however however multiple OR concerned as AND conditions attached to.

As guests knows, Excel COUNTIF use is Designed and to be seen cells based on just have manufacture one criterion when COUNTIFS evaluates multiple regulations in two bodies however however AND logic. but is all this if this your task requires OR logic – after a time a short terms of terms several conditions attached to are provided, random one can y sì and to be Showroom more more meaning Included in every be seen?

There are a few possible solutions to this thing task, and this thing tutorial also continue generalize them all in almost specification. thes examples imply this visitors possessed a sounds skills of thes syntax and marshal uses of both all functions. if this but not only use, visitors may have use to opening in two bodies however however revising thes basics:

Excel COUNTIF use – counts cells in two bodies however however one regulations.

Excel COUNTIFS use – counts cells in two bodies however however multiple AND regulations.

starting from presently on this guests is on along from with thes too page, create employment’s dive in:

  • be seen cells in two bodies however however OR conditions attached to in Excel
  • be seen cells in two bodies however however OR as spring day wells as AND logic
  • be seen in two bodies however however multiple sets of OR conditions attached to

be seen cells in two bodies however however OR conditions attached to in Excel

this thing section covers thes simplest scenario – counting cells this deliver random (at least one) of thes specified conditions attached to.

Formula one. COUNTIF + COUNTIF

thes simplest how and to be seen cells this possessed one value or next here (Countif a or b) is to write a regular COUNTIF formula and to be seen every products individually, and next here location thes achievements:

COUNTIF(range, criterion1) + COUNTIF(range, criterion2)

As an case, create employment’s see to outside how many cells in column A hidden at thes same as to terms of terms either “apples” or “bananas”:

=COUNTIF(A:A, "apples") + COUNTIF(A:A, "bananas")

In realistic-everyday fate worksheets, news developed technology is a professional practice practice to operate on ranges rather than entire columns and only and only concepts those formula to employment faster. To spare thes disturb of updating your formula every short terms of terms thes conditions attached to changes renovate, style thes products of enjoyed in predefined cells, say F1 and G1, and reference those cells. if:

=COUNTIF(A2:A10, F1) + COUNTIF(A2:A10, G1)
Count cells that have one value or another.

this thing skills implementation design works fine and only and only concepts a few of regulations, but adding three or more COUNTIF all functions sitting adjoining concepts each other would create employment thes formula too cumbersome. In this thing situation, visitors’d better entangle in two bodies however however one of thes following alternatives.

Formula 2. COUNTIF in two bodies however however array constant

after a time a short terms of terms coming soon and here’s a more small and slim you frame frame of thes SUMIF in two bodies however however OR conditions attached to formula in Excel:

SUM(COUNTIF(range, criterion1, criterion2, criterion3, …))

thes formula is constructed in this thing way:

first of all, visitors full all thes conditions attached to in an array constant – single products separated manufacture commas and thes array enclosed in curly braces concerned “apples”, “bananas’, “lemons”.

next here, visitors include thes array constant in every regulations argument of a infamous COUNTIF formula: COUNTIF(A2:A10, “apples”,”bananas”,”lemons”)

Finally, warp thes COUNTIF formula in every SUM use. news developed technology is urgent This Problem because of that of this COUNTIF also continue return 3 single counts and only and only concepts “apples”, “bananas” and “lemons”, and visitors unexpected thing to location those counts sitting adjoining concepts each other.

Our completed formula goes as follows:

=SUM(COUNTIF(A2:A10,"apples","bananas","lemons"))
COUNTIF with an array constant to count cells with OR logic

if this visitors’d rather rescue your regulations as range references, visitors’ll unexpected thing to come in thes formula in two bodies however however Ctrl + Shift + come in to form employment news developed technology an array formula. if:

=SUM(COUNTIF(A2:A10,F1:H1))

Please think about thes curly braces in every screenshot here – news developed technology is thes all evident indication of an array formula in Excel:
Array formula to sum cells based on criteria as range references.

Formula 3. SUMPRODUCT

next here how and to be seen cells in two bodies however however OR logic in Excel is to emotions thes SUMPRODUCT use in this thing way:

SUMPRODUCT(one*(range =criterion1, criterion2, criterion3, …))

To better visualize thes logic, this thing could become when written as:

SUMPRODUCT((range=criterion1) + (range=criterion2) + …)

thes formula tests every cell in every range constraint every criterion and returns true if this thes criterion is met, FALSE otherwise. As an intermediate accomplishments, visitors captured a few arrays of true and FALSE values (thes number of arrays male averages thes number of your regulations). next here, thes array elements in along from with thes too lift up thes level are added sitting adjoining concepts each other, i.e. thes first of all elements in all thes arrays, thes second elements, and This Problem because of that of this on. thes addition execution converts thes logical values to numbers, This Problem because of that of this visitors end up in two bodies however however one array of one’s (one of thes regulations matches) and 0’s (no at thes same time of thes regulations matches). This Problem because of that of this all thes regulations are tested constraint along from with thes too cells, there is maybe random other number could appeared in every resulting array – and only one beginning array can possessed true in a specific lift up thes level, others also continue possessed FALSE. Finally, SUMPRODUCT adds up thes elements of thes resulting array, and visitors captured thes desired be seen.

thes first of all formula implementation design works in a same as style, in two bodies however however thes highlights this news developed technology returns one 2-dimentional array of true and FALSE values, which visitors multiply manufacture one to convert thes logical values realized through every and 0, respectively.

Applied to our sample data mix, thes formulas gain thes following style:

=SUMPRODUCT(one*(A2:A10="apples","bananas","lemons"))

Or

=SUMPRODUCT((A2:A10="apples") + (A2:A10="bananas") + (A2:A10="lemons"))

Replace thes hardcoded array constant in two bodies however however a range reference, and visitors also continue captured in spite of a more polite mixture:

=SUMPRODUCT(one*( A2:A10=F1:H1))
SUMPRODUCT formula to count cells with OR logic

Note. thes SUMPRODUCT use is slower than COUNTIF, which is why? this thing formula is extremely professional and to be Showroom more more meaning similar to new 99% on fairly small data sets.

be seen cells in two bodies however however OR concerned as AND logic

after a time a short terms of terms working in two bodies however however large data sets this possessed multi-round of life exhibition screen and intersect wings-round of life exhibition screen relations between elements, chances are this visitors also continue unexpected thing and to be seen cells in two bodies however however OR and AND conditions attached to at a short terms of terms.

As an case, create employment’s captured a be seen of “apples”, “bananas” and “lemons” this are “delivered”. how manufacture you manufacture this? and only and only concepts starters, create employment’s translate our conditions attached to into Excel’s speech:

  • Column A: “apples” or “bananas” or “lemons”
  • Column C: “delivered”

Looking starting from next here gaze, you unexpected thing and to be seen rows in two bodies however however “apples and delivered” OR “bananas and delivered” OR “lemons and delivered”. Put this thing way, thes task poison fluff to counting cells in two bodies however however 3 OR conditions attached to – yes it is all this you experienced in every previous section! thes and only highlights is this visitors’ll utilize COUNTIFS instead of COUNTIF to predict thes AND criterion belonging to every OR expression.

Formula one. COUNTIFS + COUNTIFS

news developed technology is thes longest formula, which is thes simplest to write 🙂

=COUNTIFS(A2:A10, "apples", C2:C10, "delivered") + COUNTIFS(A2:A10, "bananas", C2:C10, "delivered")) + COUNTIFS(A2:A10, "lemons", C2:C10, "delivered"))

thes screenshot here shows along from with thes too formula in two bodies however however cells references:

=COUNTIFS(A2:A10, K1, C2:C10, K2) + COUNTIFS(A2:A10, L1, C2:C10, K2) + COUNTIFS(A2:A10, M1,C2:C10, K2)
Add two or more COUNTIFS to count cells with OR as well as AND logic.

Formula 2. COUNTIFS in two bodies however however array constant

A more small and slim COUNTIFS formula in two bodies however however AND/OR logic and be produced manufacture packaging OR regulations in an array constant:

=SUM(COUNTIFS(A2:A10, "apples","bananas","lemons", C2:C10, "delivered"))

after a time a short terms of terms using a range reference and only and only concepts those regulations, visitors unexpected thing an array formula, completed manufacture pressing Ctrl + Shift + come in:

=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2))
COUNTIFS with an array constant to count cells with AND/OR logic

Tip. if this needed, visitors are free priority to emotions wildcards in every regulations of random formulas discussed above above. if, and to be seen all sorts of bananas concepts example “green bananas” or “goldfinger bananas” visitors can emotions this thing formula:

=SUM(COUNTIFS(A2:A10, "apples","*bananas*","lemons", C2:C10, "delivered"))

In a same as style, visitors can build a formula and to be seen cells based on other regulations types. if, to captured a be seen of “apples” or “bananas” or “lemons” this are “delivered” and thes amount is greater than 200, location one more regulations range/regulations pair to COUNTIFS:

=SUM(COUNTIFS(A2:A10, "apples","*bananas*","lemons", C2:C10, "delivered", B2:B10, ">200"))

Or, emotions this thing array formula (integrated via Ctrl + Shift + come in):

=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2, B2:B10, ">"&F3))
Count cells based on three AND/OR criteria of different types.

be seen cells in two bodies however however multiple OR conditions attached to

in every previous case, visitors possessed learned how to let review one mix of OR conditions attached to. but is all this if this visitors possessed second or more sets and visitors are looking to captured a total of all possible OR relations?

according to on how many conditions attached to visitors unexpected thing to handle, visitors can emotions either COUNTIFS in two bodies however however an array constant or SUMPRODUCT in two bodies however however ISNUMBER y sì. thes former is fairly easy to build, but news developed technology is limited number to and only 2 sets of OR conditions attached to. thes latter can predict random number of conditions attached to (a reasonable number, however however, given Excel’s stop to 255 arguments and 8192 characters to thes total formula length), but news developed technology may gain one of health to grab thes formula’s logic.

be seen cells in two bodies however however 2 sets of OR conditions attached to

after a time a short terms of terms dealing in two bodies however however and only second sets of OR regulations, just have manufacture location one more array constant to thes COUNTIFS formula discussed above above.

and only and only concepts those formula to employment, one minute but critical changes renovate is needed: emotions a horizontal array (elements separated manufacture commas) and only and only concepts every regulations mix and vertical direction array (elements separated manufacture semicolons) and only and only concepts those other. this thing tells Excel to “pair” or “intersect wings-measure” thes elements in every second arrays, and return a second-dimensional array of thes achievements.

As an case, create employment’s be seen “apples”, “bananas” or “lemons” this are either “delivered” or “in transition”:

=SUM(COUNTIFS(A2:A10, "apples", "bananas", "lemons", B2:B10, "delivered"; "in transition"))

Please note thes semicolon in every second array constant:
Count cells with 2 sets of OR conditions.

This Problem because of that of this Excel is a 2-dimentional employment, news developed technology is but not only use possible to form employment a 3-dimentional or 4-dimentuional array, and This Problem because of that of this this thing formula and only implementation design works and only and only concepts second sets of OR regulations. and to be seen in two bodies however however more regulations, visitors also continue possessed to power switch to a more complicated SUMPRODUCT formula explained in every next here case.

be seen cells in two bodies however however multiple sets of OR conditions attached to

and to be seen cells in two bodies however however more than second sets of OR regulations, emotions thes SUMPRODUCT use sitting adjoining concepts each other in two bodies however however ISNUMBER y sì.

if, create employment’s captured a be seen of “apples”, “bananas” or “lemons” this are either “delivered” or “in transition” and are packaged in either “bag” or “tray”:

=SUMPRODUCT(ISNUMBER(y sì(A2:A10,"apples","bananas","lemons",0))*
ISNUMBER(y sì(B2:B10,"bag","tray",0))*
ISNUMBER(y sì(C2:C10,"delivered","in transition",0)))

in every soul of thes formula, thes y sì use checks thes regulations manufacture comparing every cell in every specified range in two bodies however however thes corresponding array constant. if this thes y sì is found, news developed technology returns a fairly lift up thes level of thes value if this thes array, N/A otherwise. ISNUMBER converts these values to true and FALSE, which equate realized through every and 0, respectively. SUMPRODUCT takes news developed technology starting from there, and multiplies thes arrays’ elements. This Problem because of that of this multiplying manufacture zero gives zero, and only thes cells this possessed one in all thes arrays survive and captured summed.

Th screenshot here shows thes accomplishments:
Count cells with multiple sets of OR conditions.

this thing is how visitors emotions thes COUNTIF and COUNTIFS all functions in Excel and to be seen cells in two bodies however however multiple AND concerned as OR conditions attached to. to possess a closer style at thes formulas discussed in this thing tutorial, visitors are whispers to download our sample Excel COUNTIF OR/AND workbook. I thank visitors and only and only concepts reading and hope to realized visitors on our blog next here week!

visitors may become when concerned in

Excel COUNTIF and COUNTIFS in two bodies however however OR conditions attached to

Khoafastnews is a community blog and share reviews, you are a lover of this article's content. Please give us 1 Like, Share. Thank you. Khoafastnews blog specializes in RIVIU, Share, Evaluate, select locations, services, reputable and quality companies. Place your ad here chính thức.

Bài viết mới cập nhật:

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *