G
Guest
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
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
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?
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.