Counting with multible conditions in a table

G

Guest

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
 
S

Sandy Mann

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

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
G

Guest

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
 
S

Sandy Mann

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
G

Guest

;O)

It works. Thank you /lotta

Sandy Mann said:
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
S

Sandy Mann

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

--
Regards,

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 

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.

Ask a Question

Top