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

Firm	Header1	Header2
Firm1	0,5	0,1
Firm2	0,5	0,38
Firm3	0,1	0,3
Firm4	0,7	0,35
Firm5	0,6	0,5
Firm6	0,2	0,6
Firm7	0,6	0,4
Firm8	0,8	0,2
Firm9	0,1	0,9

<tbody>
</tbody>

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

A B C

Firm	Header1	Header2
Firm2	0,5	0,38
Firm3	0,1	0,3
Firm4	0,7	0,35
Firm6	0,2	0,6
Firm7	0,6	0,4
Firm8	0,8	0,2

<tbody>
</tbody>

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!

<colgroup><col width="71"></colgroup><tbody>
</tbody>

<colgroup><col><col span="2"><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

<tbody>
</tbody>

 

[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

Firm	Header1	Header2
Firm2	0,5	-0,38
Firm3	0,1	0,3
Firm4	0,7	0,35
Firm6	0,2	-0,6
Firm7	0,6	0,4
Firm8	0,8	0,2

<tbody>
</tbody>

 

[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.