Visible filed in filter data how to use countif

[marko80m]

Can someone explain me how to implement countif function(more criteria) on filter data. This formula is not ok
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-MIN(ROW(B2:B50)),,1))*(B2:B50="Quality")) because there is a mistake in offset!
I have
Firm A B C D
1
.
.
.
800 800 800 800 800
I use filter to select couple of firm
Than from column A I want firms that A>=0,4 that is not problem in all data but in filter is problem.
From second column I want firm that meet first criteria and that is filed second criteria B is between 0,3 and 0,4!
=COUNTIFS(Sheet2!$A$3:$A$813;">=0,4";Sheet2!B3:B813;">=0,3";Sheet2!B3:B813;"<0,4") this formula not works in filter data.
Can someone help me!

 

[Marcelo Branco]

Say your data is like this

A B C[TABLE="width: 144"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]Firm
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Header1
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Header2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Firm1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,1
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Firm2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,38
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Firm3
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,3
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Firm4
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,7
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,35
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Firm5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,5
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Firm6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,2
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,6
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Firm7
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,4
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Firm8
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,8
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Firm9
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,1
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0,9
[/TD]
[/TR]
</tbody>[/TABLE]

And you filter selecting Firms 2, 3, 4, 6, 7 and 8 getting this

A B C
[TABLE="width: 144"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Firm
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Header1
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Header2
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Firm2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,5
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,38
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Firm3
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,1
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,3
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Firm4
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,7
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,35
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Firm6
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,6
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Firm7
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,6
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,4
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Firm8
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,8
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0,2
[/TD]
[/TR]
</tbody>[/TABLE]

To count column B >= 0.4 try this
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$2:$A$10,ROW($A$2:$A$10)-ROW($A$2),0,1))),--($B$2:$B$10>=0.4))
that returns 4

To count column B >= 0.4 AND column C between 0.3 and 0.4 try
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$2:$A$10,ROW($A$2:$A$10)-ROW($A$2),0,1))),--($B$2:$B$10>=0.4),--($C$2:$C$10>=0.3),--($C$2:$C$10<=0.4))
that returns 3

Hope this is what you need

M.

 

[marko80m]

Marcelo,
But this formula is not working on my computer. OFFSET is flash and need some corrections. Data are organized just as you presented them above.
I hope someone can explain to me what is wrong and why?
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$3:$A$813,ROW($A$3:$A$813)-ROW($A$3),0,1))),--($C$3:$C$813>=0,4),--($G$3:$G$813>=0,3),--($G$3:$G$813<0,4))

 

[Marcelo Branco]

It should work.

What Excel version are you using? Is it an US version?

Maybe you need to change the argument separator from "," to ";" (trying to guess...)

M.

 

[marko80m]

It should work.

What Excel version are you using? Is it an US version?

Maybe you need to change the argument separator from "," to ";" (trying to guess...)

M.

I use Excel 2010 Europe(Serbia) and try both formula
=SUMPRODUCT(--(SUBTOTAL(3,OFFSET($A$3:$A$813,ROW($A$3:$A$813)-ROW($A$3),0,1))),--($D$3:$D$813>=0.4),--($E$3:$E$813>=0.3),--($E$3:$E$813<=0.4)
=SUMPRODUCT(--(SUBTOTAL(3;OFFSET($A$3:$A$813;ROW($A$3:$A$813)-ROW($A$3);0;1)));--($D$3:$D$813>=0,4);--($E$3:$E$813>=0,3);--($E$3:$E$813<=0,4)
and of course You are right formula is working but I have another problem:
In some column I have #DIV/0! and formula is not working is it possible, because obvious you are expert, to correct this thing!
In any case, I am very grateful! [TABLE="width: 71"]
<colgroup><col width="71"></colgroup><tbody>[TR]
[TD="class: xl65, width: 71, align: center"][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 694"]
<colgroup><col><col span="2"><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 10"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 684"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try to correct your data wrapping the formula with IFERROR (Excel 2007 or higher)

For example if you have
=A1/B1
and B1=0 it results in #DIV/0!

So use this
=IFERROR(A1/B1;"")

You should correct the data not the SUMPRODUCT formula

M.

 

[marko80m]

Thank you, I do not know what to say except one more Thanks!

Thanks a lot!!!

 

[Marcelo Branco]

Thank you, I do not know what to say except one more Thanks!

Thanks a lot!!!

You are very welcome and thanks for the feedback.

M.

 

[marko80m]

Marcelo,
I have another problem related to the calculation of absolute numbers. I wish to be -0.3 and 0.3 are counted as 0.3 on how this turned into a excel.
Hoping to help me, and with this...
=SUMPRODUCT(--(SUBTOTAL(3;OFFSET($A$3:$A$813;ROW($A$3:$A$813)-ROW($A$3);0;1)));--($D$3:$D$813>=0,4);--($E$3:$E$813>=0,3);--($E$3:$E$813<=0,4)
A B C
[TABLE="class: cms_table, width: 144"]
<tbody>[TR]
[TD="class: cms_table_xl65, width: 64, bgcolor: transparent"]Firm[/TD]
[TD="class: cms_table_xl65, width: 64, bgcolor: transparent"]Header1[/TD]
[TD="class: cms_table_xl65, width: 64, bgcolor: transparent"]Header2[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent"]Firm2[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]0,5[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]-0,38
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent"]Firm3[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]0,1[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]0,3[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent"]Firm4[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]0,7
[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]0,35[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent"]Firm6[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]0,2[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]-0,6
[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent"]Firm7[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]0,6[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]0,4[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl65, bgcolor: transparent"]Firm8[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]0,8[/TD]
[TD="class: cms_table_xl65, bgcolor: transparent, align: right"]0,2


[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(--(SUBTOTAL(3;OFFSET($A$3:$A$813;ROW($A$3:$A$813)-ROW($A$3);0;1)));--($D$3:$D$813>=0.4);--(ABS($E$3:$E$813)>=0.3);--(ABS($E$3:$E$813)<=0.4))

M.