Numbers COUNTIF from another sheet?

[iamJaymes]

I've been trying for half hour to get numbers to count the number of cells in another sheet that match the same cell in my other sheet.

Basically:

SHEET 1: Table 1

Name - address - Number of Orders etc
Name1 - Address1 -

SHEET 2: Table 1
Name - order info etc
Name1 - orderinfo1
Name1 - orderinfo1
Name2 - ETC

I'm trying to get sheet 1: table 1 to give me a number of times Name1 occurs in Sheet 2: table. So sheet 1 can basically keep records of how many orders that person has.

Hope that makes sense.

Tried google, searched the user manual and can't find a thread anywhere or an answer anywhere. Little help?

Thanks

[jemesouviens]

in excel you'd do this in sheet1:

name__________¦ number of orders
----------------------------------------------------------
name in cell A2_¦ =COUNTIF(Sheet2!A:A,Sheet1!A2)


where the names are in column A on sheet2.

Hope you can adapt it for Numbers!

[snberk103]

You'll probably have better luck posting this on the Apple Support Community Forums. There is one there specifically for iWork apps... maybe even as specific as Numbers. They are a lot less "fun" over there, compared to MRs - but you will find a higher concentration of people asking for - and receiving - technical help.

That said.... I'm going to offer a suggestion - but it is off the top of my head. I'm not at my work computer yet. If this doesn't work, post back.

Are you typing the cell references in by hand when you build the formula, or are you clicking the cells you want in the range? When I'm building a formula I click the cells I want rather than type the reference.

It might also help us if you posted the formula you are trying to use.

Cheers

[iamJaymes]

I've tried it both ways.

=COUNTIF('Orders - Finances' :: Table 1 :: $B2:$B37, “=B2")

Is what I have and i don't understand why it doesn't work.

I've changed B2 to the contents of B2 and it lists 1, which is correct, then why I change it back to B2 it says 0. So I don't understand.

I've tried writing the long version of the sheet with B2 and still nothing. =/

[snberk103]

OK... I've been playing around with it. I know what the problem is, but I don't know how to fix it.

COUNTIF is counting the number of times the text string ' B2 ' appears, and not how often the contents of cell B2 appears.

I've tried various ways to get it to read the contents of a cell - but no luck yet. I'm comfortable using Numbers, but I'm far from being a 'power user'. Try over at the Apple Support Forums.... the power users hang out there. I'll play a little bit more... but if you find a solution, please post it. I'd be curious to know what it is.

Cheers

[snberk103]

Originally Posted by iamJaymes

I've been trying for half hour to get numbers to count the number of cells in another sheet that match the same cell in my other sheet.
...

I think I figured it out. Get rid of the quotes and equal sign after the comma.

So.... =COUNTIF('Orders - Finances' :: Table 1 :: $B2:$B37, “=B2")
becomes.... =COUNTIF('Orders - Finances' :: Table 1 :: $B2:$B37, B2)

It seems to default to "equals to" if there is no equal sign. The double quotes was telling the formula to use the exact text string. In your case, find how often the string B2 appeared. By taking the quotes out we are back to using the contents of B2. I have no idea how to count for > or < conditions .... luckily you want an exact match.

Hope this helps.....

[iamJaymes]

PERFECT!

Thank you!

[jemesouviens]

so, Numbers uses the same syntax as Excel but with scope operators defining the range - interesting.