Countif error

[devabharambe]

Hi

I got error for counit formula :COUNTIF(INDEX(DATA_Count,MATCH(B2,Data_Range,0)),">0") following table

pl suggest correct formula

regards
devendra


Sheet1 Data :

[TABLE="width: 669"]
<colgroup><col><col><col span="7"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]Sr.No[/TD]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: right"]01-Oct-17[/TD]
[TD="align: right"]113[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]02-Oct-17[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]03-Oct-17[/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]04-Oct-17[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]05-Oct-17[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]06-Oct-17[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD="align: right"]07-Oct-17[/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]08-Oct-17[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]09-Oct-17[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]10-Oct-17[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]


Output Data on sheet 2

[TABLE="width: 294"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Date[/TD]
[TD]Nos of purchase material[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]01-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]02-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]03-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]04-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]05-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]06-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]07-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]08-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]09-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]11-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]12-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]13-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]14-Oct-17[/TD]
[TD="align: center"]#REF![/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Wat is the the expected outcome for 01-Oct-17?

 

[devabharambe]

Thank you for your valuable reply

expected outcomes is as below (zero value not counted)

01-OCt -17 5
02-Oct- 17 6

 

[Aladin Akyurek]

Thank you for your valuable reply

expected outcomes is as below (zero value not counted)

01-OCt -17 5
02-Oct- 17 6

Thanks.

In B2 of Sheet2 enter:

Either...

=IFERROR(COUNTIFS(INDEX(Sheet1!$C$2:$I$11,MATCH($A2,Sheet1!$B$2:$B$11,0),0),">0"),"")

Or...

=IFERROR(INDEX(FREQUENCY(INDEX(Sheet1!$C$2:$I$11,MATCH($A2,Sheet1!$B$2:$B$11,0),0),0),2),"")

The second should be a tad faster.

 

[devabharambe]

Thanks

 

[Aladin Akyurek]

Thanks

You are welcome.