Wrapping Up Multiple Counts And Criteria Ranges By Utilizing COUNTIFS()

Utilizing COUNTIFS()

In Count the quantity of Excel records that fall between two dates, I utilized the COUNTIFS() capacity to tally the quantity of dates that fell between two dates (comprehensive of the dates themselves). In spite of the fact that the COUNTIFS() work enables you to indicate numerous tally and criteria runs, it’s adaptability isn’t promptly evident. (This capacity is accessible in Excel 2007 and 2010.)

Before we investigate that thought, how about we pause for a moment to survey COUNTIFS(). This capacity utilizes the accompanying linguistic structure to indicate numerous criteria:

COUNTIFS(countrange1, criteria1, [countrange2, criteria2]…)

Be that as it may, neither countrangex nor criteriax require reference a similar range or information write.

The accompanying Excel sheet outlines this thought by tallying the quantity of dates that fall between a begin and end date. (The one admonition being that this capacity neglects to restore the right esteem on the off chance that you transpose the two criteria dates.) The accompanying equation include the quantity of dates A2:A9 that are equivalent to or more prominent than the date in F1 and equivalent to or not as much as the date in F2:

=COUNTIFS($A$2:$A$9, “>=”&$F$1,$A$2:$A$9,”<=”&$F$2)

For this situation, the check ranges are the same and the two criteria are dates. Five dates fall between the begin and end dates.

office setup

Presently, how about we assume that you need as far as possible the hunt to particular work force (segment B). What now? Keep in mind when I said that COUNTIFS() was sufficiently adaptable to deal with different check ranges and information writes? The least difficult arrangement is to include a third check and criteria range to the COUNTIFS(), as demonstrated as follows. The third arrangement of reaches in this equation alludes to the staff in segment B – that is an alternate check extend and the criteria is a string, not a date esteem.

=COUNTIFS($A$2:$A$9,”>=”&$F$1,$A$2:$A$9,”<=”&$F$2,B$2:B$9,”=”&$F$4)

office setup 2

Underneath, you can see this case assessed in aide sections. Just a single record meets each of the three criteria articulations. There is one restriction, which turns out to be more evident in this configuration. The tally ranges should all be a similar size. For example in the event that you reference A2:A9 and A2:A6,or A2:A9 and B2:B6, the capacity restores a blunder.

install office

Blogs : office.com/setup

Leave a Reply

Your email address will not be published. Required fields are marked *