countif both conditions in a row of 2 cells exists

[Guest]

example of what I'm trying to do:
"if the number in G6 is <800 and H6 is >=20 then count the row as 1"

 

[RagDyeR]

Try this:

=(G6<800)*(H6>=20)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

example of what I'm trying to do:
"if the number in G6 is <800 and H6 is >=20 then count the row as 1"

 

[Dave Peterson]

=sumproduct(--(g1:g999<800),--(h1:h999>=20))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Guest]

That works Great! Now how could this be done with a range. For example,
looking at each 2 cell row in a range and counting as 1 only if both
condition exist.
G6:G29 <800 and H6:H29>=20

 

[RagDyeR]

Then just use what Dave posted.

His formula will total over a range.
Follow his instructions to adjust the ranges he used to the ranges you are
using.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

That works Great! Now how could this be done with a range. For example,
looking at each 2 cell row in a range and counting as 1 only if both
condition exist.
G6:G29 <800 and H6:H29>=20

 

[Guest]

I've tried it. Can't get it to work

Then just use what Dave posted.

His formula will total over a range.
Follow his instructions to adjust the ranges he used to the ranges you are
using.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

That works Great! Now how could this be done with a range. For example,
looking at each 2 cell row in a range and counting as 1 only if both
condition exist.
G6:G29 <800 and H6:H29>=20

 

[Dave Peterson]

Post what you tried.

I've tried it. Can't get it to work

 

[Guest]

tried a few more times. got it to work. Been looking at this thing too long.
Can I do something similar to just use 2 cells to be counted as a row only
if they are not blank?"

 

[RagDyeR]

I don't understand what you're trying to say.

 

[Dave Peterson]

maybe...

=sumproduct(--(g1:g999<>""),--(h1:h999<>""))

But this will treat formulas that return "" as "blank".