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 the 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 allow me to shorten my formula.

Nov 26, 2022 in Others 267 views

## 1 answer to this question.

If the Excel version you are using supports dynamic arrays (ms365), you can utilize a boolean structure inside SUMPRODUCT() or plain SUM():

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

I hope this helps you.

• 63,160 points

## How to use COUNTIFS with multiple conditions and columns in Excel?

Use  SUMPRODUCT and double unay operators: =SUMPRODUCT ...READ MORE

## Excel How to Remove Duplicate Rows in multiple of 3 Same Value

Put the following formula into a helper ...READ MORE

## How to set formula in Excel with POI?

Microsoft Excel only stores en US formula ...READ MORE

## Excel COUNTIF "Column D" year equals 2015 and Column L equals "15 or greater"

COUNTIFS() with an S allows the ability of multiple ...READ MORE

## Excel Formula Query on Summing Annual Leave Days and Half Days

Try Something like =SUM(COUNTIF(\$A\$1:\$A\$8,"A"),COUNTIF(\$A\$1:\$A\$8,"HA")/2) - total count ...READ MORE

## Newbie needs Excel suggestions with a simple script

Formula to Count the Number of Occurrences ...READ MORE

## Excel: Count distinct numerical values if string condition matches

FILTER Shipper and Month based on Employee ...READ MORE