CountIf not working for Spilled data

[trprasad78]

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


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

 

[trprasad78]

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

 

[pecoflyer]

Please post a sample sheet. ( no pics please)
(and BTW it is customary in free forums to wait about 24 hrs before bumping)

 

[AliGW]

Which version of Excel are you using?

 

[trprasad78]

office 365 desktop version (2021)

Which version of Excel are you using?

 

[AliGW]

Attach a workbook.

 

[trprasad78]

Attach a workbook.

sample attached,

thank you

 

[AliGW]

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.

 

[Fluff13]

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

 

[trprasad78]

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.

 

[trprasad78]

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

 

[Fluff13]

Glad to help & thanks for the feedback