| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007: Create a pivot table where data is in multible sheets | Excel Worksheet Functions | |||
| Help with counting multiple conditions | Excel Discussion (Misc queries) | |||
| Counting on two conditions, one with a range | Excel Worksheet Functions | |||
| SUM(IF(multible conditions)) | Excel Worksheet Functions | |||
| counting based on 2 conditions | Excel Discussion (Misc queries) | |||
Linear Mode
