CODEWITHSUNDEEP
excelexcel-formulacountif
yosif
yosif

Reputation: 7

How to simplify adding multiple countifs formula in excel

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.

Upvotes: 0

Views: 503

Answers (1)

JvdV
JvdV

Reputation: 76000

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

Upvotes: 1

Related Questions