11 Ví dụ hay về hàm SUMPRODUCT Thảo luận trong 'Hàm và công thức Excel' bắt đầu bởi DOSNET, 13 Tháng một 2009.

11 Ví dụ hay về hàm SUMPRODUCT

Thảo luận trong 'Hàm và công thức Excel' bắt đầu bởi DOSNET, 13 Tháng một 2009.

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()))

Nhấp chuột vào đây để mở rộng...

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"))

Nhấp chuột vào đây để mở rộng...

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.

Nhấp chuột vào đây để mở rộng...

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)

Nhấp chuột vào đây để mở rộng...

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"))

Nhấp chuột vào đây để mở rộng...

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="")))

Nhấp chuột vào đây để mở rộng...

Share on Google Plus

About sorftphanmem