问题描述:

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.

Thanks in advance!

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