Example 2: To count the number of sales in 3 locations of service since a given time period.
Solution: In it's basic elements, this is a simple test. If the date to be tested against is in a cell it would be a simple

=SUMPRODUCT((C5:C309>$A$1))*(H5:H309="A"))

But this formula shows a technique to use embedded date strings that works, as far as I am aware, in all international versions of Excel.

=SUMPRODUCT((C5:C309>(--("2004/05/31")))*(H5:H309="A"))

Example 3: Instead of typing the multiple criteria into the formula, can I have them typed into cells, and just reference the cells?

Solution: This seeemd a simple request to which a solution of

=SUMPRODUCT((B5:B63=L1:N1)*(C5:C63))

was suggested.

This failed because the requester wanted the criteria in a column, not a row, so this required the TRANSPOSE function to incorporate in SUMPRODUCT. This was the result

=SUMPRODUCT((B5:B63=TRANSPOSE(P46:P48))*(C5:C63))

which, because it uses the TRANSPOSE function, has to be entered as an array formula.
Example 4: I originally had this,

=SUMIF(J2:J196,J209,L2:L196)

but I need to have these extra ranges aggregated.
R2:R196,U2:U196,V2:V196,Z2:Z196

Solution: This could easily be solved by having separate SUMPRODUCT functions for each of the separate test ranges, but with a bit of ingenuity, it can be resolved in one, by using the '+' operator.

=SUMPRODUCT(--(J2:J196=J209),L2:L196+R2:R196+U2:U196+V2:V196+Z2: Z196)
Example 5: Find the occurrences of a string, value of 'good', in a range A1:A100. Some of the cells could include leading and/or trailing spaces, or even HTML non -breaking spaces.

Solution: The basic count of the string is very simple. Allowing for leading and trailing spaces is also handled by including TRIM in the foirmula. However, TRIM doesn't handle the HTML non-breaking spaces, these have to be extracted from the range being tested with the SUBSTITUTE function.good

=SUMPRODUCT(--(TRIM(SUBSTITUTE(A1:A100,CHAR(160),""))="good"))

Example 6: Count the number of unique values in a range.

Solution: The first version works if the whole range, A1:A20, is occupied

=SUMPRODUCT(1/COUNTIF(A1:A20,A1:A20))

However, this returns #DIV/0 if any of the range is blank. This can be corrected with

=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))

And finally, to overcome a bug in the implicit intersection of COUNTIF/SUMIF 1st argument with that argument's parent worksheet's used range., which can also return #DIV/0, we can use

=SUMPRODUCT((A1:A20<>"")/(COUNTIF(A1:A20,A1:A20)+(A1:A20="")))

DOSNET

13-01-09, 09:33 AM

Tiếp tục từ VD 7 đến VD 11.

Example 7:

Count the instances of either of two conditions being met in two different ranges.For this example, consider the range A1:A10 with countries, and B1:B10 with continents, C1:C10 with a flag saying whether they are G7 countries or not. We want to count the number of countries that are in Europe, or are G7 countries.

Solution: To count the number of countries that are eoither in Europe, or G7 countries, we could use

=SUMPRODUCT((B1:B10="Europe")+(C1:C10="Y"))

The problem with this is that it will double-count the countries tin Europe that are also G7 countries. This can be overcome with

which uses a SUMPRODUCT to calculate the number of countries that are both in Europe and G7 countries, and is then subtracted from the double-counting formula.

Example 8: Count the instances of more than one value in a given range. This example is counting how many Fords and Chryslers are in the range A1:A10.

Solution: This can be solved by using OR as described in the prevuious example, but on a single range, that is

=SUMPRODUCT((A1:A10="Ford")+(A1:A10="Chrysler"))

But in this in stance, as we are looking for two values in a single range, it is better to test against an array of values, or

=SUMPRODUCT(--(A1:A10={"Ford","Chrysler"}))

Example 9: Having a range of stock numbers, A1:A10, corresponding sites in B1:B10, and stock numbers in C1:C10, we need to count how many items of a particular part at a particular site. This would be a straight-forward SUMPRODUCT normally, but in this case, the stock number contains a number of components, so the part id is embedded within this. SUMIF can use wildcards, but only for one test, but SUMPRODUCT doesn't support wildcards directly.

Solution: To solve this problem, we can use the FIND function to test whetehr our part number if embebbed within the stock number. The ISNUMBER function is used to test whether it is found or not (avoiding the dreaded #VALUE error)

=SUMPRODUCT(--(ISNUMBER(FIND("ATN",A1:A10))),--(B1:B10="Birmingham"),--(C1:C10))
FIND
is case sensitive, if case sensitivity is not required, use SEARCH instead.

Example 10: Count the number of a certain day between two dates, excluding any holidays that may fall on those days.

Solution: SUMPRODUCT can be used to calculate the number of a particular day between two dates. For example, assuming that the two dates are in cells A1 and A2, this formula returns the number of Wednesdays bewteen those two dates.

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=4))

This solution utilises the fact that as Excel stores dates as serial numbers from 1st Jan 1900, the two dates can be used in an INDIRECT function to 'virtually' load all of the dates into rows, which can then be tested using the WEEKDAYand the ROW function to determine whether any of those row dates are the day in question. This does of course place a limit on the later date, which is 06-Jun-2079, as Excel is restricted to 65336 rows.

The NETWORKDAYS function provides a facility to exclude holidays in the count. Again, we can achieve this with our function by adding a test against the holdays. Assuming that the holidays are in a named range, holidays, we would use

This might seem unnecessary, as we could more easily use the NETWORKDAYS function, but it does offer one small advantage over that function, it doesn't matter what order the dates are in. It could also be used to exclude any 1,2, 3 or whatever days, not just the Saturdays and Sundays, by changing the weekday value.

Example 11:

Sum only the visible cells that match a certain criteria. For instance, in a range A1:A100, sum all cells that have a value of "North" in B1:B100, where some rows are not visble due to a Data Filter having been applied on the data.

Solution:
This solution takes advantage of the function which ignores non-visible cells.
The first part is a straight-forward conditional test on range B1:B100 for a value of 'North, and the sum of the cells A1:A100

--($B$1:$B$100="North"),$A$1:$A$10

The counting of the visible cells is more complicated. As mentioned above, it uses SUBTOTAL, together with ROW, INDEX and OFFSET functions, like so