## 动力学知识库

Sorry if this has been answered but hours of Google-ing has revealed no elegant solution.

I have a sheet that looks like this only there are hundreds of rows.

``+---+---+---+---+-----+| A | B | C | D | E |+---+---+---+---+-----+| X | a | Y | b | 1.2 || X | b | Y | c | 1.5 || Y | c | Z | c | 1.8 |+---+---+---+---+-----+``

My goal is to count rows where for example the character in column A="X", character in column C="X" and characters in columns B and D are not the same (B!=D). The first part is working...

``COUNTIFS(A:A ,"X" , C:C, "X")``

but I can't figure out how to compare two cells that are both part of a range but on the same line. The following seems to compare the whole ranges...

``COUNTIFS(A:A ,"X" , C:C, "X", B:B, D:D)``

Additionally, I'd like to sum the values in column E for similarly defined groups.

Solved it! Added the following formula to each row in column F...

``````=(B:B=D:D)+0
``````

That will return 1 or 0 depending on whether the contents of B and D is matching. And that is something I can add to my existing formula.

``````UNTIFS(A:A ,"X" , C:C, "X", F:F, 0)
``````