Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countfig?
Ok I know that I need to use Countfig or something like it. But can you use
Countfig to look at data in 2 different columns. My early question was: I have a spreadsheet which is used for a hotel guest info log. There is a column in each day for which country the guest is from, how they heard about us, etc. What I want to do is at the bottom of the s/sheet, have a key for the country codes, and then have a tally next to it: Country Country Key Totals Ger GERMANY Fr FRANCE Eng England Ire IRISH J JAPAN I know you can use the sort function and do it manually that way, but I wondered if there was a function that would auto find for instance 'GER' in a specific column, and count the amount of times it occures, then put that figure of times it occurs into a seperate designated cell? So I was kindly informed that I needed to use Countfig. What I want to do now is look up in colun C the country, then compare it to a yes / no column and have a box for that. I'm not sure that makes sense. I want to look for how many German people stayed, and I want to know how many of then were members, and how many of them were non members. I'm hoping this makes sense because I really really need to know how to make tis work! Cheers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countfig?
...........A..........B
1......Ger........Yes 2......Fr...........No 3......Ger........No 4......Ger........Yes 5......Eng........No To count the total number of German guests: D1 = Ger E1 = Yes =COUNTIF(A1:A5,D1) To count the total number of German guests that are members (Yes): =SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1)) -- Biff Microsoft Excel MVP "J4shaw" wrote in message ... Ok I know that I need to use Countfig or something like it. But can you use Countfig to look at data in 2 different columns. My early question was: I have a spreadsheet which is used for a hotel guest info log. There is a column in each day for which country the guest is from, how they heard about us, etc. What I want to do is at the bottom of the s/sheet, have a key for the country codes, and then have a tally next to it: Country Country Key Totals Ger GERMANY Fr FRANCE Eng England Ire IRISH J JAPAN I know you can use the sort function and do it manually that way, but I wondered if there was a function that would auto find for instance 'GER' in a specific column, and count the amount of times it occures, then put that figure of times it occurs into a seperate designated cell? So I was kindly informed that I needed to use Countfig. What I want to do now is look up in colun C the country, then compare it to a yes / no column and have a box for that. I'm not sure that makes sense. I want to look for how many German people stayed, and I want to know how many of then were members, and how many of them were non members. I'm hoping this makes sense because I really really need to know how to make tis work! Cheers |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countfig?
Hi
Thanks for this, I am a little confused though. Any chance you could explain it as though I'm an utter idiot? :) "T. Valko" wrote: ...........A..........B 1......Ger........Yes 2......Fr...........No 3......Ger........No 4......Ger........Yes 5......Eng........No To count the total number of German guests: D1 = Ger E1 = Yes =COUNTIF(A1:A5,D1) To count the total number of German guests that are members (Yes): =SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1)) -- Biff Microsoft Excel MVP "J4shaw" wrote in message ... Ok I know that I need to use Countfig or something like it. But can you use Countfig to look at data in 2 different columns. My early question was: I have a spreadsheet which is used for a hotel guest info log. There is a column in each day for which country the guest is from, how they heard about us, etc. What I want to do is at the bottom of the s/sheet, have a key for the country codes, and then have a tally next to it: Country Country Key Totals Ger GERMANY Fr FRANCE Eng England Ire IRISH J JAPAN I know you can use the sort function and do it manually that way, but I wondered if there was a function that would auto find for instance 'GER' in a specific column, and count the amount of times it occures, then put that figure of times it occurs into a seperate designated cell? So I was kindly informed that I needed to use Countfig. What I want to do now is look up in colun C the country, then compare it to a yes / no column and have a box for that. I'm not sure that makes sense. I want to look for how many German people stayed, and I want to know how many of then were members, and how many of them were non members. I'm hoping this makes sense because I really really need to know how to make tis work! Cheers |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countfig?
You said you wanted to count the number of German guests and then count the
number of German guests that were members. Using this sample data that's in the range A1:D5: ...........A..........B 1......Ger........Yes 2......Fr...........No 3......Ger........No 4......Ger........Yes 5......Eng........No To count the German guests: D1 = Ger =COUNTIF(A1:A5,D1) This formula returns 3. 3 German guests counted in A1:A5. To count the German guests that are also members, members being designated by "Yes" in B1:B5. D1 = Ger E1 = Yes =SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1)) This formula returns 2. 2 German guests that are also members. -- Biff Microsoft Excel MVP "J4shaw" wrote in message ... Hi Thanks for this, I am a little confused though. Any chance you could explain it as though I'm an utter idiot? :) "T. Valko" wrote: ...........A..........B 1......Ger........Yes 2......Fr...........No 3......Ger........No 4......Ger........Yes 5......Eng........No To count the total number of German guests: D1 = Ger E1 = Yes =COUNTIF(A1:A5,D1) To count the total number of German guests that are members (Yes): =SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1)) -- Biff Microsoft Excel MVP "J4shaw" wrote in message ... Ok I know that I need to use Countfig or something like it. But can you use Countfig to look at data in 2 different columns. My early question was: I have a spreadsheet which is used for a hotel guest info log. There is a column in each day for which country the guest is from, how they heard about us, etc. What I want to do is at the bottom of the s/sheet, have a key for the country codes, and then have a tally next to it: Country Country Key Totals Ger GERMANY Fr FRANCE Eng England Ire IRISH J JAPAN I know you can use the sort function and do it manually that way, but I wondered if there was a function that would auto find for instance 'GER' in a specific column, and count the amount of times it occures, then put that figure of times it occurs into a seperate designated cell? So I was kindly informed that I needed to use Countfig. What I want to do now is look up in colun C the country, then compare it to a yes / no column and have a box for that. I'm not sure that makes sense. I want to look for how many German people stayed, and I want to know how many of then were members, and how many of them were non members. I'm hoping this makes sense because I really really need to know how to make tis work! Cheers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|