I want to count the number of cells that meet two conditions:

1. sheet ABC's A2:A100 should be equal to the value of sheet XYC cell A8
2. the cell value in range D2:M100 = 1

Originally, I tried to use this formula:

=COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$D$2:$M$100,1)

But this gave me error #VALUE

I then decided to use the following formula to count each column separately and add them together.

=COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$D$2:$D$100,1) 
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$E$2:$E$100,1) 
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$F$2:$F$100,1) 
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$G$2:$G$100,1) 
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$H$2:$H$100,1) 
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$I$2:$I$100,1) 
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$J$2:$J$100,1) 
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$K$2:$K$100,1) 
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$L$2:$L$100,1) 
COUNTIFS(ABC!$A$2:$A$100,XYC!A8,ABC!$M$2:$M$100,1)

I am wondering if there are any other ways that allows me to shorten my formula?

Thank you.

CodePudding user response：

You can use a boolean structure inside SUMPRODUCT() or just SUM() if your version of Excel supports dynamic arrays (ms365):

=SUMPRODUCT((ABC!A2:A100=XYC!A8)*(ABC!D2:M100=1))