T
TMF in MN
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
The value should be 2
A B
1 1 12
2 2 75
3 1 86
4 1 16
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
PCLIVE
One way:
=SUMPRODUCT(--(A1:A100=1)*(B1:B100<20))
This requires that you specify a range, not the entire column.
Regards,
Paul
=SUMPRODUCT(--(A1:A100=1)*(B1:B100<20))
This requires that you specify a range, not the entire column.
Regards,
Paul
B
Bob Phillips
=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)
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)
E
Epinn
I think
=SUMPRODUCT((A1:A100=1)*(B1:B100<20))
is also fine - without coercing.
Epinn
One way:
=SUMPRODUCT(--(A1:A100=1)*(B1:B100<20))
This requires that you specify a range, not the entire column.
Regards,
Paul
=SUMPRODUCT((A1:A100=1)*(B1:B100<20))
is also fine - without coercing.
Epinn
One way:
=SUMPRODUCT(--(A1:A100=1)*(B1:B100<20))
This requires that you specify a range, not the entire column.
Regards,
Paul
B
Bob Phillips
is also fine - without coercing.
What makes you say without coercing?
E
Epinn
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
What makes you say without coercing?
Epinn
is also fine - without coercing.
What makes you say without coercing?
B
Bob Phillips
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)
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
What makes you say without coercing?
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
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
is also fine - without coercing.
What makes you say without coercing?
E
Epinn
Yes, I know the first part. I have to think about the second. *All* operators, eh? I am only familiar with + - * /
Epinn
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)
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
What makes you say without coercing?
Epinn
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)
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
is also fine - without coercing.
What makes you say without coercing?
B
Bob Phillips
N, ^
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
Yes, I know the first part. I have to think about the second. *All*
operators, eh? I am only familiar with + - * /
Epinn
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)
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
What makes you say without coercing?
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
Yes, I know the first part. I have to think about the second. *All*
operators, eh? I am only familiar with + - * /
Epinn
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)
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
is also fine - without coercing.
What makes you say without coercing?