Countif function and non-contiguous cells
apple Verified ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I am having trouble using the COUNTIF function with nonadjacent cells. If I count contiguous cells in a column (i.e. =COUNTIF(A1:A10, "=ATK"), I get the appropriate result. If I count non-contiguous cells (i.e. =COUNTIF(A1,A3,A6,A10, "=ATK"), I get the error message "the formula contains a function with the wrong number of arguments." What am I doing wrong?
Thanks
8 replies
Sign in or join Beachball Help to reply.
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Hello
You are just inventing a syntax which is not the one designed by the authors 😉
The COUNTIF function counts the number of cells in a range that satisfy a condition.
COUNTIF(test-range, condition)
_test-range: The range of cells to be tested._
condition: The conditional test that results in a logical TRUE or FALSE.Examples
If cells A1:A8 contain 1, 2, 3, 4, 5, 6, 7, 8:COUNTIF(A1:A8, "<4") returns 3.
COUNTIF(A1:A8, ">4") returns 4.
COUNTIF(A1:A8, ">=4") returns 5.Yvan KOENIG (from FRANCE lundi 11 février 2008 21:16:27)
- 0
pw1840 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Jeff,
The on-line documentation for COUNTIF states:
COUNTIF(test-range, condition)
Counts the number of cells in a range that satisfy a condition.It definitely states a range as a parameter and as your error message points out, multiple ranges are not recognized. It looks like you will have to sum a series of COUNTIF's to get your result.
=COUNTIF(A3:A7,TRUE)
COUNTIF(B5,TRUE)+COUNTIF(B19:C21,TRUE). . .pw
- 0
Jeff Sakihara ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Thanks, pw
Isn't there an easier way to do this? I use a spreadsheet to make an assignment calendar. I want to count the number of times everyone in our group is at each location, and during which calendar day. Therefore, I need to count the number of times a text entry is repeated in various cells scattered throughout the spreadsheet. Any hints?
Thanks again
Jeff
- 0
Jeff Sakihara ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Other functions seem to use this syntax OK. If for example you use =SUM(A4,A6,A8,A10), it will add the cells A4, A6, A8 and A10. If you use the COUNTIF to count a the number of times X appears in a contiguous column from A4 to A10, you can use =COUNTIF(A4:A10, "=X"). But if you want to count the number of times X appears in cells A4, A6, A8 and A10, you cannot use =COUNTIF(A4,A6,A8,A10,"=X). Any suggestions?
ThanksJeff
- 0
WWJD ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Concatenate several COUNTIF statements for each cell range COUNITF(...)+COUNTIF(...) etc, etc..
Regards,
- 0
pw1840 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Jeff,
I'm having a difficult time picturing the structure of this spreadsheet and several questions come to mind (in no particular order). What are you counting? Is it some sort of text code that represents student, location, and time? Why are you trying to skip around different cells to get your count? Does this spreadsheet contain schedules for more than one day? etc. If we better understood this situation, readers may be able to help, even offer alternate solutions.
Incidentally, never assume that if one function accepts parameters in a certain fashion or order that all other functions will follow suit.
pw
- 0
Jerrold Green1 ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Jeff,
If the condition you are testing for is only present where you are looking for it, and not present in other parts of the Table, you could test (COUNTIF) the entire table rather than just isolated segments of it. You haven't said whether this is true or not, so I'm only guessing.
Jerry
- 0
KOENIG Yvan ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Hello
The only suggestion which I may offer is:
*Read the description of a function before using it.*
One may guess many syntaxes for a function but the only ones which are correct are the ones which are described in the Help.
Here, we are users trying to help other users but we can't change the design of an application.
Yvan KOENIG (from FRANCE mardi 12 février 2008 21:54:04)