# CountIf not working for Spilled data

##### Member
I am getting value error if use countif in J15 , But refer from outside I am getting out put.

if I use following formula in the same cell , I am getting #Value! error

=COUNTIF(INDEX(FILTER('Raw data'!\$E\$3:\$P\$72,'Raw data'!\$B\$3:\$B\$72="Inactive"),0,MATCH('Working sheet'!G4,'Raw data'!\$E\$2:\$P\$2,0)),">0")

please let me know, if my question is not clear.

(and BTW it is customary in free forums to wait about 24 hrs before bumping)

Which version of Excel are you using?

office 365 desktop version (2021)
Which version of Excel are you using?

Attach a workbook.

#### Attachments

• Inventory.xlsx
23.8 KB · Views: 5
You are getting an error with this:

=COUNTIF(INDEX(FILTER('Raw data'!\$E\$3:\$P\$72,'Raw data'!\$B\$3:\$B\$72="Inactive"),0,MATCH('Working sheet'!G4,'Raw data'!\$E\$2:\$P\$2,0)),">0")

because the worksheets referenced in it aren't there. We can't help when there is crucial data missing from the sample sheet - sorry.

The formula doesn't work as the filter function returns an array & countifs needs a range.
However you can use countifs like
=COUNTIFS(INDEX('Raw data'!\$E\$3:\$P\$72,,XMATCH('Working sheet'!G4,'Raw data'!\$E\$2:\$P\$2,0)),">0",'Raw data'!\$B\$3:\$B\$72,"Inactive")

The formula doesn't work as the filter function returns an array & countifs needs a range.
However you can use countifs like
=COUNTIFS(INDEX('Raw data'!\$E\$3:\$P\$72,,XMATCH('Working sheet'!G4,'Raw data'!\$E\$2:\$P\$2,0)),">0",'Raw data'!\$B\$3:\$B\$72,"Inactive")
Thank you so much its working fine.

You are getting an error with this:

=COUNTIF(INDEX(FILTER('Raw data'!\$E\$3:\$P\$72,'Raw data'!\$B\$3:\$B\$72="Inactive"),0,MATCH('Working sheet'!G4,'Raw data'!\$E\$2:\$P\$2,0)),">0")

because the worksheets referenced in it aren't there. We can't help when there is crucial data missing from the sample sheet - sorry.
Thank you

Glad to help & thanks for the feedback