I'm trying to do something that seems easy, but just don't know how to:
I want to count the number of times column B is not blank AND column A is a specified value. ex:
A B
C 1
C
C 1
M 1
C
C 1
So I'm looking for the number of times that "C" AND "1" occur.
Any help is greatly appreciated.
Thanks!
Try...
=SUMPRODUCT(--($A$1:$A$6="C"),--($B$1:$B$6=1))
OR
=SUMPRODUCT(--($A$1:$A$6=D1),--($B$1:$B$6=E1))
...where D1 contains your first criterion, such as C, and E1 contains your second criterion, such as 1.
Hope this helps!
Originally Posted by ckiraly
Two options...
(a)
=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)
where E2 houses a condition like C and F2 a condition like 1.
(b)
In C2 enter & copy down:
=A2&"#"&B2
Then invoke:
=COUNTIF($C$2:$C$10,E2&"#"&F2)
ckiraly wrote:
> I'm trying to do something that seems easy, but just don't know how to:
>
> I want to count the number of times column B is not blank AND column A
> is a specified value. ex:
>
> A B
>
> C 1
> C
> C 1
> M 1
> C
> C 1
>
> So I'm looking for the number of times that "C" AND "1" occur.
>
> Any help is greatly appreciated.
>
> Thanks!
>
>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Thank you both. I will try those solutions out.
The solutions worked great! Now I want to one up it. How can do this to see if column B is not blank?
To count where col A="C" and Col B is not blank change
--($B$1:$B$6=1)
to
--($B$1:$B$6<>"")
Thank you very much! Slowly but surely I'll get the hang of excel.
Two options...
(a)
=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)
where E2 houses a condition like C and F2 a condition like 1.
(b)
In C2 enter & copy down:
=A2&"#"&B2
Then invoke:
=COUNTIF($C$2:$C$10,E2&"#"&F2)
ckiraly wrote:
> I'm trying to do something that seems easy, but just don't know how to:
>
> I want to count the number of times column B is not blank AND column A
> is a specified value. ex:
>
> A B
>
> C 1
> C
> C 1
> M 1
> C
> C 1
>
> So I'm looking for the number of times that "C" AND "1" occur.
>
> Any help is greatly appreciated.
>
> Thanks!
>
>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Two options...
(a)
=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)
where E2 houses a condition like C and F2 a condition like 1.
(b)
In C2 enter & copy down:
=A2&"#"&B2
Then invoke:
=COUNTIF($C$2:$C$10,E2&"#"&F2)
ckiraly wrote:
> I'm trying to do something that seems easy, but just don't know how to:
>
> I want to count the number of times column B is not blank AND column A
> is a specified value. ex:
>
> A B
>
> C 1
> C
> C 1
> M 1
> C
> C 1
>
> So I'm looking for the number of times that "C" AND "1" occur.
>
> Any help is greatly appreciated.
>
> Thanks!
>
>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Two options...
(a)
=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)
where E2 houses a condition like C and F2 a condition like 1.
(b)
In C2 enter & copy down:
=A2&"#"&B2
Then invoke:
=COUNTIF($C$2:$C$10,E2&"#"&F2)
ckiraly wrote:
> I'm trying to do something that seems easy, but just don't know how to:
>
> I want to count the number of times column B is not blank AND column A
> is a specified value. ex:
>
> A B
>
> C 1
> C
> C 1
> M 1
> C
> C 1
>
> So I'm looking for the number of times that "C" AND "1" occur.
>
> Any help is greatly appreciated.
>
> Thanks!
>
>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Two options...
(a)
=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)
where E2 houses a condition like C and F2 a condition like 1.
(b)
In C2 enter & copy down:
=A2&"#"&B2
Then invoke:
=COUNTIF($C$2:$C$10,E2&"#"&F2)
ckiraly wrote:
> I'm trying to do something that seems easy, but just don't know how to:
>
> I want to count the number of times column B is not blank AND column A
> is a specified value. ex:
>
> A B
>
> C 1
> C
> C 1
> M 1
> C
> C 1
>
> So I'm looking for the number of times that "C" AND "1" occur.
>
> Any help is greatly appreciated.
>
> Thanks!
>
>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Two options...
(a)
=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)
where E2 houses a condition like C and F2 a condition like 1.
(b)
In C2 enter & copy down:
=A2&"#"&B2
Then invoke:
=COUNTIF($C$2:$C$10,E2&"#"&F2)
ckiraly wrote:
> I'm trying to do something that seems easy, but just don't know how to:
>
> I want to count the number of times column B is not blank AND column A
> is a specified value. ex:
>
> A B
>
> C 1
> C
> C 1
> M 1
> C
> C 1
>
> So I'm looking for the number of times that "C" AND "1" occur.
>
> Any help is greatly appreciated.
>
> Thanks!
>
>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Two options...
(a)
=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)
where E2 houses a condition like C and F2 a condition like 1.
(b)
In C2 enter & copy down:
=A2&"#"&B2
Then invoke:
=COUNTIF($C$2:$C$10,E2&"#"&F2)
ckiraly wrote:
> I'm trying to do something that seems easy, but just don't know how to:
>
> I want to count the number of times column B is not blank AND column A
> is a specified value. ex:
>
> A B
>
> C 1
> C
> C 1
> M 1
> C
> C 1
>
> So I'm looking for the number of times that "C" AND "1" occur.
>
> Any help is greatly appreciated.
>
> Thanks!
>
>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Two options...
(a)
=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)
where E2 houses a condition like C and F2 a condition like 1.
(b)
In C2 enter & copy down:
=A2&"#"&B2
Then invoke:
=COUNTIF($C$2:$C$10,E2&"#"&F2)
ckiraly wrote:
> I'm trying to do something that seems easy, but just don't know how to:
>
> I want to count the number of times column B is not blank AND column A
> is a specified value. ex:
>
> A B
>
> C 1
> C
> C 1
> M 1
> C
> C 1
>
> So I'm looking for the number of times that "C" AND "1" occur.
>
> Any help is greatly appreciated.
>
> Thanks!
>
>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Two options...
(a)
=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)
where E2 houses a condition like C and F2 a condition like 1.
(b)
In C2 enter & copy down:
=A2&"#"&B2
Then invoke:
=COUNTIF($C$2:$C$10,E2&"#"&F2)
ckiraly wrote:
> I'm trying to do something that seems easy, but just don't know how to:
>
> I want to count the number of times column B is not blank AND column A
> is a specified value. ex:
>
> A B
>
> C 1
> C
> C 1
> M 1
> C
> C 1
>
> So I'm looking for the number of times that "C" AND "1" occur.
>
> Any help is greatly appreciated.
>
> Thanks!
>
>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Two options...
(a)
=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)
where E2 houses a condition like C and F2 a condition like 1.
(b)
In C2 enter & copy down:
=A2&"#"&B2
Then invoke:
=COUNTIF($C$2:$C$10,E2&"#"&F2)
ckiraly wrote:
> I'm trying to do something that seems easy, but just don't know how to:
>
> I want to count the number of times column B is not blank AND column A
> is a specified value. ex:
>
> A B
>
> C 1
> C
> C 1
> M 1
> C
> C 1
>
> So I'm looking for the number of times that "C" AND "1" occur.
>
> Any help is greatly appreciated.
>
> Thanks!
>
>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Two options...
(a)
=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)
where E2 houses a condition like C and F2 a condition like 1.
(b)
In C2 enter & copy down:
=A2&"#"&B2
Then invoke:
=COUNTIF($C$2:$C$10,E2&"#"&F2)
ckiraly wrote:
> I'm trying to do something that seems easy, but just don't know how to:
>
> I want to count the number of times column B is not blank AND column A
> is a specified value. ex:
>
> A B
>
> C 1
> C
> C 1
> M 1
> C
> C 1
>
> So I'm looking for the number of times that "C" AND "1" occur.
>
> Any help is greatly appreciated.
>
> Thanks!
>
>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
Two options...
(a)
=SUMPRODUCT(($A$2:$A$10=E2)+0,($B$2:$B$10=F2)+0)
where E2 houses a condition like C and F2 a condition like 1.
(b)
In C2 enter & copy down:
=A2&"#"&B2
Then invoke:
=COUNTIF($C$2:$C$10,E2&"#"&F2)
ckiraly wrote:
> I'm trying to do something that seems easy, but just don't know how to:
>
> I want to count the number of times column B is not blank AND column A
> is a specified value. ex:
>
> A B
>
> C 1
> C
> C 1
> M 1
> C
> C 1
>
> So I'm looking for the number of times that "C" AND "1" occur.
>
> Any help is greatly appreciated.
>
> Thanks!
>
>
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks