11 Ví dụ hay về hàm SUMPRODUCT (1 người xem)

[DOSNET]

Giới thiệu với các bạn 11 Ví dụ hay để hiểu thêm về cách dùng hàm Sumproduct, nhân tiện nhờ các bạn dịch giúp ra tiếng Việt giúp.
Nguồn

Example 1: Count the number of items where the date, in A42:A407 is earlier than today, and J42:J407 is equal to a variable array of values

Solution:The date test is handled with ($A$42:$A$407<TODAY()). The variable array of values is setup in a range, and this is used in conjunction with the MATCH and ISNUMBER functions.

=SUMPRODUCT((ISNUMBER(MATCH(J42:J407,Fred,0)))*($A$42:$A$407<TODAY()))

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(P4648))*(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]

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

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

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.

=SUMPRODUCT(--(ISNUMBER(SEARCH("ATN",A1:A10))),--(B1:B10="Birmingham"),--(C1:C10))

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

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=4),--(COUNTIF(holidays,ROW(INDIRECT($A$1&":"&A2)))=0))

We could also simulate the NETWORKDAYS function to count the number of days between two dates, excluding Saturdays and Sundays amd holidays using

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))<>1),(--(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))<>7)),--(COUNTIF(holidays,ROW(INDIRECT($A$1&":"&A2)))=0))

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

--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1:$A$100)-ROW(INDEX($A$1:$A$100,1,1)),0))=1)

The total formula then becomes

=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1:$A$100)-ROW(INDEX($A$1:$A$100,1,1)),0))=1),--($B$1:$B$100="North"),$A$1:$A$10)

 

[haonlh]

Công thức cuối cùng của ví dụ 11 là:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1 :$A$100)-ROW(INDEX($A$1:$A$100,1,1)),0))=1),--($B$1:$B$100="North"),$A$1:$A$10)
Có thể (và nên) thay mọi 2 dấu -- bằng 1 dấu - ; vì sẽ có 2 dãy đổi dấu nên tích của chúng không đổi.
Nếu có lẻ cặp 2 dấu -- thì kết quả âm. Thêm dấu - và đầu công thức để đổi dấu nó.
Tôi cũng chưa thử công thức trên, nhưng nên thay như vậy để giảm thời gian tính: mỗi dấu - sẽ đổi dấu 100 số (theo công thức trên), 4 dấu là 400 lần đổi dấu. Tức là thời gian để đổi dấu giảm 2 lần.

 

[cadafi]

Công thức cuối cùng của ví dụ 11 là:
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET(INDEX($A$1:$A$100,1,1),ROW($A$1 :$A$100)-ROW(INDEX($A$1:$A$100,1,1)),0))=1),--($B$1:$B$100="North"),$A$1:$A$10)
Có thể (và nên) thay mọi 2 dấu -- bằng 1 dấu - ; vì sẽ có 2 dãy đổi dấu nên tích của chúng không đổi.
Nếu có lẻ cặp 2 dấu -- thì kết quả âm. Thêm dấu - và đầu công thức để đổi dấu nó.
Tôi cũng chưa thử công thức trên, nhưng nên thay như vậy để giảm thời gian tính: mỗi dấu - sẽ đổi dấu 100 số (theo công thức trên), 4 dấu là 400 lần đổi dấu. Tức là thời gian để đổi dấu giảm 2 lần.

Tôi lại nghĩ khác, "--" chẳng qua nó là một phép toán chứ không phải là ký hiệu để đổi dấu, và thông thường người ta thường dùng *1 hoặc -- hoặc value() hoặc ^1 để làm một phép tính với một mảng biểu thức logic True/False để trả về giá trị có thể tính toán được là {0,1}

Một lý do nữa để không dùng "-" mà nên dùng "--" là khi lập công thức, dùng "--" người lập công thức không cần bận tâm có bao nhiêu điều kiện, điều kiện lẻ hay chẵn để thêm dấu trừ nữa!.

Một lý do nữa là đối với công thức phức tạp và dài, ngăn cách giữa các điều kiện bằng dấu "--" sẽ dễ nhận biết hơn là dấu "-" ở giữa một rừng công thức!

 

[DOSNET]

Nếu có lẻ cặp 2 dấu -- thì kết quả âm. Thêm dấu - và đầu công thức để đổi dấu nó.
Tôi cũng chưa thử công thức trên, nhưng nên thay như vậy để giảm thời gian tính: mỗi dấu - sẽ đổi dấu 100 số (theo công thức trên), 4 dấu là 400 lần đổi dấu. Tức là thời gian để đổi dấu giảm 2 lần.

Bạn đang quan tâm tới giải pháp cải thiện tốc độ của SUMPRODUCT, có lẽ bạn hiểu hơi nhầm một chút phần mình tô đậm, mình đống ý với những gì Ca_dafi đã giải thích, ngoài ra dùng --( trong một số trường hợp giúp ta tính được mà không báo lỗi #VALUE khi trong mảng có cả giá trị kiểu TEXT, đây là điểm hay nhất mình thường áp dụng !

Trong 11 Ví dụ trên có những chỗ bản thân mình cũng chưa hiểu rõ lắm, chẳng hạn ở VD 1

  [COLOR=DarkRed] =SUMPRODUCT((ISNUMBER(MATCH(J42:J407,[B][COLOR=Red]Fred[/COLOR][/B],0)))*($A  $42:$A$407<TODAY()))[/COLOR]

Fred có phải là một Name ?

 

[yeudoi]

Bạn đang quan tâm tới giải pháp cải thiện tốc độ của SUMPRODUCT, có lẽ bạn hiểu hơi nhầm một chút phần mình tô đậm, mình đống ý với những gì Ca_dafi đã giải thích, ngoài ra dùng --( trong một số trường hợp giúp ta tính được mà không báo lỗi #VALUE khi trong mảng có cả giá trị kiểu TEXT, đây là điểm hay nhất mình thường áp dụng !

Trong 11 Ví dụ trên có những chỗ bản thân mình cũng chưa hiểu rõ lắm, chẳng hạn ở VD 1

  [COLOR=DarkRed] =SUMPRODUCT((ISNUMBER(MATCH(J42:J407,[B][COLOR=Red]Fred[/COLOR][/B],0)))*($A  $42:$A$407<TODAY()))[/COLOR]

Fred có phải là một Name ?

Ngoài ra khi dùng dấu "--" có tác dụng tăng tốc độ tính toán.

 

[socola7287]

Tai Example 6: Em dang su dung ham =SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&"")) de dem co bao nhieu so khong trung nhau. Ket qua la chinh xac
Y nghia va ket qua thi e da bit, e muon hoi nguyen tac van hanh cua no nhu the nao, mong cac bac send cau tra loi nhe
E thanks truoc nhe!

 

[basang76]

chào các bạn ! tôi nhận thấy hàm SUMPRODUCT này rất hay . nhưng tôi đọc bài các bạn hướng dẫn mà thấy khó quá . Bởi vì các bạn viết giải thích bằng tiếng anh mà tôi thì mù tịt tiếng anh. làm phiền các bạn viết bằng tiếng việt được không ? cảm ơn các bạn nhiều! Qua đây làm ơn hãy gỡ rối giúp tôi với . tôi có một bảng tính trong đó có các cột ghi tên các dân tộc . tôi muốn đếm số lượng dân tộc nói chung ( ví dụ như: dao, tày , nùng , thái . trong đó trừ người kinh ra ) bạn nào có thể giúp tôi được không / xin chân thành cảm ơn các bạn ! Nếu có thể hãy gửi cho tôi theo địa chỉ basang76@ymail.com

 

[EXCEL2020]

chào các bạn ! tôi nhận thấy hàm SUMPRODUCT này rất hay . nhưng tôi đọc bài các bạn hướng dẫn mà thấy khó quá . Bởi vì các bạn viết giải thích bằng tiếng anh mà tôi thì mù tịt tiếng anh. làm phiền các bạn viết bằng tiếng việt được không ? cảm ơn các bạn nhiều! Qua đây làm ơn hãy gỡ rối giúp tôi với . tôi có một bảng tính trong đó có các cột ghi tên các dân tộc . tôi muốn đếm số lượng dân tộc nói chung ( ví dụ như: dao, tày , nùng , thái . trong đó trừ người kinh ra ) bạn nào có thể giúp tôi được không / xin chân thành cảm ơn các bạn ! Nếu có thể hãy gửi cho tôi theo địa chỉ basang76@ymail.com

Bạn đưa file lên mọi người sẽ dễ dàng giúp bạn. Theo bạn diễn tả thì chỉ cần countif là được rồi. Ví dụ minh họa[TABLE="width: 371"]
[TR]
[TD]Tên[/TD]
[TD]dân tộc[/TD]
[TD]đếm[/TD]
[/TR]
[TR]
[TD]ngyuen van a[/TD]
[TD]thai[/TD]
[TD]COUNTIF($G$11:$G$15;G11)[/TD]
[/TR]
[TR]
[TD]nguyen van b[/TD]
[TD]tay[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]nguyen thi c[/TD]
[TD]nung[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]nguyen thi d[/TD]
[TD]thai[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]ngyen van f[/TD]
[TD]tay[/TD]
[TD="align: right"]2[/TD]
[/TR]
[/TABLE]