Countfig?

[J4shaw]

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

 

[T. Valko]

...........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))

 

[J4shaw]

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]

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 A15:

...........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.