Counting with multible conditions in a table
 

Hi,
After several hours of trials and no success I urgently need help.

I have the following table:

col1 Jan Feb march .............
a n x x
b x x
a x x n
d x x y
b y n n
..
..

I want to do the following list:

A1 = The number of all x and n in column Jan matching col1 = a (i.e. 2)
A2 = The number of all x and n in column Jan matching col1 = b (i.e. 1)

I cannot get COUNTIF or SUMPRODUCT to work.

BR /lotta

Counting with multible conditions in a table
 

=SUMPRODUCT((B2:B6={"x","n"})*(A2:A6="a"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lotta" wrote in message
...
Hi,
After several hours of trials and no success I urgently need help.

I have the following table:

col1 Jan Feb march .............
a n x x
b x x
a x x n
d x x y
b y n n
.
.

I want to do the following list:

A1 = The number of all x and n in column Jan matching col1 = a (i.e.
2)
A2 = The number of all x and n in column Jan matching col1 = b (i.e.
1)

I cannot get COUNTIF or SUMPRODUCT to work.

BR /lotta

Counting with multible conditions in a table
 

Hi Sandy Mann,

I appreciate your quick answer but it still does not work. When I use the
formula and convert it to my table, I just change B2:B6 to I27:I36, it says
that the formula contains an error. Excel highlights {"x","n"}.
BR /lotta

"Sandy Mann" wrote:

=SUMPRODUCT((B2:B6={"x","n"})*(A2:A6="a"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lotta" wrote in message
...
Hi,
After several hours of trials and no success I urgently need help.

I have the following table:

col1 Jan Feb march .............
a n x x
b x x
a x x n
d x x y
b y n n
.
.

I want to do the following list:

A1 = The number of all x and n in column Jan matching col1 = a (i.e.
2)
A2 = The number of all x and n in column Jan matching col1 = b (i.e.
1)

I cannot get COUNTIF or SUMPRODUCT to work.

BR /lotta

Counting with multible conditions in a table
 

What did you change A2:A6 to?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Lotta" wrote in message
...
Hi Sandy Mann,

I appreciate your quick answer but it still does not work. When I use the
formula and convert it to my table, I just change B2:B6 to I27:I36, it
says
that the formula contains an error. Excel highlights {"x","n"}.
BR /lotta

"Sandy Mann" wrote:

=SUMPRODUCT((B2:B6={"x","n"})*(A2:A6="a"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lotta" wrote in message
...
Hi,
After several hours of trials and no success I urgently need help.

I have the following table:

col1 Jan Feb march .............
a n x x
b x x
a x x n
d x x y
b y n n
.
.

I want to do the following list:

A1 = The number of all x and n in column Jan matching col1 = a
(i.e.
2)
A2 = The number of all x and n in column Jan matching col1 = b
(i.e.
1)

I cannot get COUNTIF or SUMPRODUCT to work.

BR /lotta

Counting with multible conditions in a table
 

Hi,
I changed it to GBTS. If I change {"x","n"} to {"x"} ut works.
BR /lotta

"RagDyer" wrote:

What did you change A2:A6 to?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Lotta" wrote in message
...
Hi Sandy Mann,

I appreciate your quick answer but it still does not work. When I use the
formula and convert it to my table, I just change B2:B6 to I27:I36, it
says
that the formula contains an error. Excel highlights {"x","n"}.
BR /lotta

"Sandy Mann" wrote:

=SUMPRODUCT((B2:B6={"x","n"})*(A2:A6="a"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lotta" wrote in message
...
Hi,
After several hours of trials and no success I urgently need help.

I have the following table:

col1 Jan Feb march .............
a n x x
b x x
a x x n
d x x y
b y n n
.
.

I want to do the following list:

A1 = The number of all x and n in column Jan matching col1 = a
(i.e.
2)
A2 = The number of all x and n in column Jan matching col1 = b
(i.e.
1)

I cannot get COUNTIF or SUMPRODUCT to work.

BR /lotta

Counting with multible conditions in a table
 

Hi Again,
What I mean it works for x but I miss the counting for n.
/lotta

"Lotta" wrote:

Hi,
I changed it to GBTS. If I change {"x","n"} to {"x"} ut works.
BR /lotta

"RagDyer" wrote:

What did you change A2:A6 to?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Lotta" wrote in message
...
Hi Sandy Mann,

I appreciate your quick answer but it still does not work. When I use the
formula and convert it to my table, I just change B2:B6 to I27:I36, it
says
that the formula contains an error. Excel highlights {"x","n"}.
BR /lotta

"Sandy Mann" wrote:

=SUMPRODUCT((B2:B6={"x","n"})*(A2:A6="a"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lotta" wrote in message
...
Hi,
After several hours of trials and no success I urgently need help.

I have the following table:

col1 Jan Feb march .............
a n x x
b x x
a x x n
d x x y
b y n n
.
.

I want to do the following list:

A1 = The number of all x and n in column Jan matching col1 = a
(i.e.
2)
A2 = The number of all x and n in column Jan matching col1 = b
(i.e.
1)

I cannot get COUNTIF or SUMPRODUCT to work.

BR /lotta

Counting with multible conditions in a table
 

Hi Lotta,

I copied your example data and pasted it into I1:L6, this put the a,b, & d's
in Column I and the x & n's in Column J

=SUMPRODUCT((J2:J6={"x","n"})*(I2:I6="a"))

then returned 2.

By pasting the data and the using Text to Columns with <space as the
delimiter I had removed any spaces that may have been in the data. I added
a space after the n and that changed the return from the formula to 1 like
you are getting. The formula:

=SUMPRODUCT((TRIM(J2:J6)={"x","n"})*(TRIM(I2:I6)=" a"))

again returned 2 because it took care of the added space.

Try the second formula and if you are still having trouble post back again.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lotta" wrote in message
...
Hi Again,
What I mean it works for x but I miss the counting for n.
/lotta

"Lotta" wrote:

Hi,
I changed it to GBTS. If I change {"x","n"} to {"x"} ut works.
BR /lotta

"RagDyer" wrote:

What did you change A2:A6 to?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Lotta" wrote in message
...
Hi Sandy Mann,

I appreciate your quick answer but it still does not work. When I
use the
formula and convert it to my table, I just change B2:B6 to I27:I36,
it
says
that the formula contains an error. Excel highlights {"x","n"}.
BR /lotta

"Sandy Mann" wrote:

=SUMPRODUCT((B2:B6={"x","n"})*(A2:A6="a"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lotta" wrote in message
...
Hi,
After several hours of trials and no success I urgently need help.

I have the following table:

col1 Jan Feb march .............
a n x x
b x x
a x x n
d x x y
b y n n
.
.

I want to do the following list:

A1 = The number of all x and n in column Jan matching col1 = a
(i.e.
2)
A2 = The number of all x and n in column Jan matching col1 = b
(i.e.
1)

I cannot get COUNTIF or SUMPRODUCT to work.

BR /lotta

Counting with multible conditions in a table
 

;O)

It works. Thank you /lotta

"Sandy Mann" wrote:

Hi Lotta,

I copied your example data and pasted it into I1:L6, this put the a,b, & d's
in Column I and the x & n's in Column J

=SUMPRODUCT((J2:J6={"x","n"})*(I2:I6="a"))

then returned 2.

By pasting the data and the using Text to Columns with <space as the
delimiter I had removed any spaces that may have been in the data. I added
a space after the n and that changed the return from the formula to 1 like
you are getting. The formula:

=SUMPRODUCT((TRIM(J2:J6)={"x","n"})*(TRIM(I2:I6)=" a"))

again returned 2 because it took care of the added space.

Try the second formula and if you are still having trouble post back again.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lotta" wrote in message
...
Hi Again,
What I mean it works for x but I miss the counting for n.
/lotta

"Lotta" wrote:

Hi,
I changed it to GBTS. If I change {"x","n"} to {"x"} ut works.
BR /lotta

"RagDyer" wrote:

What did you change A2:A6 to?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Lotta" wrote in message
...
Hi Sandy Mann,

I appreciate your quick answer but it still does not work. When I
use the
formula and convert it to my table, I just change B2:B6 to I27:I36,
it
says
that the formula contains an error. Excel highlights {"x","n"}.
BR /lotta

"Sandy Mann" wrote:

=SUMPRODUCT((B2:B6={"x","n"})*(A2:A6="a"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lotta" wrote in message
...
Hi,
After several hours of trials and no success I urgently need help.

I have the following table:

col1 Jan Feb march .............
a n x x
b x x
a x x n
d x x y
b y n n
.
.

I want to do the following list:

A1 = The number of all x and n in column Jan matching col1 = a
(i.e.
2)
A2 = The number of all x and n in column Jan matching col1 = b
(i.e.
1)

I cannot get COUNTIF or SUMPRODUCT to work.

BR /lotta

Counting with multible conditions in a table
 

You're welcome, glad that you got it working.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lotta" wrote in message
...
;O)

It works. Thank you /lotta

"Sandy Mann" wrote:

Hi Lotta,

I copied your example data and pasted it into I1:L6, this put the a,b, &
d's
in Column I and the x & n's in Column J

=SUMPRODUCT((J2:J6={"x","n"})*(I2:I6="a"))

then returned 2.

By pasting the data and the using Text to Columns with <space as the
delimiter I had removed any spaces that may have been in the data. I
added
a space after the n and that changed the return from the formula to 1
like
you are getting. The formula:

=SUMPRODUCT((TRIM(J2:J6)={"x","n"})*(TRIM(I2:I6)=" a"))

again returned 2 because it took care of the added space.

Try the second formula and if you are still having trouble post back
again.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lotta" wrote in message
...
Hi Again,
What I mean it works for x but I miss the counting for n.
/lotta

"Lotta" wrote:

Hi,
I changed it to GBTS. If I change {"x","n"} to {"x"} ut works.
BR /lotta

"RagDyer" wrote:

What did you change A2:A6 to?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit
!
---------------------------------------------------------------------------
"Lotta" wrote in message
...
Hi Sandy Mann,

I appreciate your quick answer but it still does not work. When I
use the
formula and convert it to my table, I just change B2:B6 to
I27:I36,
it
says
that the formula contains an error. Excel highlights {"x","n"}.
BR /lotta

"Sandy Mann" wrote:

=SUMPRODUCT((B2:B6={"x","n"})*(A2:A6="a"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Lotta" wrote in message
...
Hi,
After several hours of trials and no success I urgently need
help.

I have the following table:

col1 Jan Feb march .............
a n x x
b x x
a x x n
d x x y
b y n n
.
.

I want to do the following list:

A1 = The number of all x and n in column Jan matching col1
= a
(i.e.
2)
A2 = The number of all x and n in column Jan matching col1
= b
(i.e.
1)

I cannot get COUNTIF or SUMPRODUCT to work.

BR /lotta