Hello All,
I want to count the number of cells in Column B that = "A", "B", "C" IF Column A = "A", "B", "C"
I can't get a SUMPRODUCT to work with multiple criteria in both columns (or a wildcard in one column).
I have tried:
=SUMPRODUCT(--(ColB={"A","B","C"})*(ColA={"A","B","C"}))
=SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="*"))
Neither work.
I could use an intermediate calculation, but I'm trying to avoid it. I could use:
=SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="A"))+SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="B"))+...
but that's messy.
Anyone any ideas? Thanks in advance
I want to count the number of cells in Column B that = "A", "B", "C" IF Column A = "A", "B", "C"
I can't get a SUMPRODUCT to work with multiple criteria in both columns (or a wildcard in one column).
I have tried:
=SUMPRODUCT(--(ColB={"A","B","C"})*(ColA={"A","B","C"}))
=SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="*"))
Neither work.
I could use an intermediate calculation, but I'm trying to avoid it. I could use:
=SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="A"))+SUMPRODUCT(--(ColB={"A","B","C"})*(ColA="B"))+...
but that's messy.
Anyone any ideas? Thanks in advance