I need a formula to count if A:A=1 and B:B<20
The value should be 2
A B
1 1 12
2 2 75
3 1 86
4 1 16

One way:

=SUMPRODUCT(--(A1:A100=1)*(B1:B100<20))

This requires that you specify a range, not the entire column.

Regards,
Paul

"TMF in MN" wrote in message
...
I need a formula to count if A:A=1 and B:B<20
The value should be 2
A B
1 1 12
2 2 75
3 1 86
4 1 16

=SUMPRODUCT(--(A1:A100=1),--(B1:B100<20))

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"TMF in MN" wrote in message
...
I need a formula to count if A:A=1 and B:B<20
The value should be 2
A B
1 1 12
2 2 75
3 1 86
4 1 16

I think

=SUMPRODUCT((A1:A100=1)*(B1:B100<20))

is also fine - without coercing.

Epinn

"PCLIVE" wrote in message ...
One way:

=SUMPRODUCT(--(A1:A100=1)*(B1:B100<20))

This requires that you specify a range, not the entire column.

Regards,
Paul

"TMF in MN" wrote in message
...
I need a formula to count if A:A=1 and B:B<20
The value should be 2
A B
1 1 12
2 2 75
3 1 86
4 1 16

"Epinn" wrote in message
...

is also fine - without coercing.

What makes you say without coercing?

Wrong term. I should be more specific and used the term "double unary." * (multiplication sign) is also coercing, right? One of these days, I'll get it right.

Epinn

"Bob Phillips" wrote in message ...

"Epinn" wrote in message
...

is also fine - without coercing.

What makes you say without coercing?

Coercing is forcing TRUE/FALSE to 1/0. All operators do that.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Wrong term. I should be more specific and used the term "double unary." *
(multiplication sign) is also coercing, right? One of these days, I'll get
it right.

Epinn

"Bob Phillips" wrote in message
...

"Epinn" wrote in message
...

is also fine - without coercing.

What makes you say without coercing?

Yes, I know the first part. I have to think about the second. *All* operators, eh? I am only familiar with + - * /

Epinn

"Bob Phillips" wrote in message ...
Coercing is forcing TRUE/FALSE to 1/0. All operators do that.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Wrong term. I should be more specific and used the term "double unary." *
(multiplication sign) is also coercing, right? One of these days, I'll get
it right.

Epinn

"Bob Phillips" wrote in message
...

"Epinn" wrote in message
...

is also fine - without coercing.

What makes you say without coercing?