Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
Go Back   MacRumors Forums > Apple Systems and Services > Mac Basics and Help

Reply
 
Thread Tools Search this Thread Display Modes
Old Jan 22, 2013, 09:38 AM   #1
iamJaymes
macrumors newbie
 
Join Date: Aug 2011
Numbers COUNTIF from another sheet?

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
iamJaymes is offline   0 Reply With Quote
Old Jan 22, 2013, 09:59 AM   #2
jemesouviens
macrumors member
 
Join Date: Oct 2012
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!

Last edited by jemesouviens; Jan 22, 2013 at 10:01 AM. Reason: psacing
jemesouviens is offline   0 Reply With Quote
Old Jan 22, 2013, 10:35 AM   #3
snberk103
macrumors 603
 
Join Date: Oct 2007
Location: An Island in the Salish Sea
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
__________________
My friends, love is better than anger. Hope is better than fear. Optimism is better than despair. So let us be loving, hopeful and optimistic. And we'll change the world. - Jack Layton
snberk103 is offline   0 Reply With Quote
Old Jan 22, 2013, 10:55 AM   #4
iamJaymes
Thread Starter
macrumors newbie
 
Join Date: Aug 2011
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. =/
iamJaymes is offline   0 Reply With Quote
Old Jan 22, 2013, 11:51 AM   #5
snberk103
macrumors 603
 
Join Date: Oct 2007
Location: An Island in the Salish Sea
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
__________________
My friends, love is better than anger. Hope is better than fear. Optimism is better than despair. So let us be loving, hopeful and optimistic. And we'll change the world. - Jack Layton
snberk103 is offline   0 Reply With Quote
Old Jan 22, 2013, 12:07 PM   #6
snberk103
macrumors 603
 
Join Date: Oct 2007
Location: An Island in the Salish Sea
Quote:
Originally Posted by iamJaymes View Post
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.....
__________________
My friends, love is better than anger. Hope is better than fear. Optimism is better than despair. So let us be loving, hopeful and optimistic. And we'll change the world. - Jack Layton
snberk103 is offline   0 Reply With Quote
Old Jan 22, 2013, 12:22 PM   #7
iamJaymes
Thread Starter
macrumors newbie
 
Join Date: Aug 2011
PERFECT!

Thank you!
iamJaymes is offline   0 Reply With Quote
Old Jan 22, 2013, 02:14 PM   #8
jemesouviens
macrumors member
 
Join Date: Oct 2012
so, Numbers uses the same syntax as Excel but with scope operators defining the range - interesting.
jemesouviens is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Systems and Services > Mac Basics and Help

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Similar Threads
thread Thread Starter Forum Replies Last Post
Numbers vs Excel when mixing text & numbers TheBlackLabEdge Mac Applications and Mac App Store 5 Mar 6, 2014 08:29 PM
sheet naming when exporting numbers file to excel daz1985 Mac Applications and Mac App Store 0 Sep 9, 2013 03:02 PM
Dialog sheet cpuin Mac Programming 0 Jun 19, 2013 04:38 AM
Plotting very small numbers in Numbers tnsmart Mac Applications and Mac App Store 2 Aug 26, 2012 05:16 PM

Forum Jump

All times are GMT -5. The time now is 05:07 AM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC