Spreadsheet formula for summation on amount of equ

问题:

Given a spreadsheet with two columns, say A and B, each containing n values under it, all text; is there a formula that allows me to fill just one cell containing the amount of equal values in columns A and B?

Example:

    A   B
    -----
1   M   M
2   L   M
3   L   L
4   M   M
5   M   L
    -----
        3

Since columns A and B both contain an M in rows 1 and 4, and a L in row 3, the result is 3. (2+1 ^^)

Thanks in advance - Spreadsheet rookie

回答1:

A simple solution is to use QUERY function in google spreadsheet:

=SUM(QUERY(A1:B5, "Select 1 where A = B"))

Or using SUMPRODUCT:

=ARRAYFORMULA(SUM(((A:A)=(B:B)) * (1) ))

回答2:

One of possible solution will be to add the following formula in column C: =N(EXACT(A1,B1)), copy it throughout the column down to the last row and then sum up the column C values using =SUM(C1:C5).

回答3:

Here we go:

 =IF(EQ(LEFT(A0, 1), "A"),
     SUM(ARRAYFORMULA(N(EXACT(TRANSPOSE(A1:A5), TRANSPOSE(B1:B5))))),
     "")

Reading: if the value in row 0 (it doesn't exist, but my example above does ;) ) is equal to the text "A", take the sum of an array N, otherwise put in an empty string. ("")

Array N is build by taking the transpose of columns A and B. (Turning them, so they look like a row) and comparing the values. (Burnash gave me the options "N" and "EXACT") The formula N transforms this into a 1 or 0.

Copy paste the formula in an entire row and what do you know... It worked! That was hellish for something so trivial.

Thanks anyway.