Q: countif
-
Aug 23, 2016 3:32 AMby Ali Jennings,Hi Ian,
Thanks but the cells I need to check are not sequential e.g. if D2 is >49 but<100 I must add 1 to the count - next cell to check for same value is G2 - next cell to check is M2 - next cell to check is Q2 and so on for total of 32 cells up to FV2.
It's a complex and big spreadsheet. Any ideas?
-
Aug 23, 2016 4:30 AMby Yellowbox,Hi Ali,
SUMIFS with three comparisons
Here is a short version of the table.
Checkboxes to mark the columns that you want to include.
I used 50 as every value to make it easier to test.
Two Header columns. With the formula in a Header column, it can refer to the whole row.
Formula in B2 =SUMIFS(2:2,2:2,">49",2:2,"<100",3:3,"=TRUE")
With this idea, you can apply it to different sized tables and to different checked boxes.
Regards,
Ian.
-
Aug 23, 2016 5:16 AMby Ali Jennings,Sorry Ian - maybe I'm not explaining my requirement. My ss is for a cricket game. The batsmen has scored runs which are keyed into a cell. If those runs are >49 and <100 then I need to add 1 to a total of "50's" in another cell. The batsmen (1 per row) will have 32 batting scores i.e. 32 different cells, not sequential, (e.g. D3,H3,L3,P3.....up to FV3) need to be tested if >49 and <100 and 1 added to the count e.g. in cell FZ3. SUMIFS does not appear to be correct as I dont want to add the value in each cell, just need to add 1 to a "counter" of value is between 49 and 100.
-
Aug 23, 2016 6:22 AMby Yellowbox,Hi Ali,
Sorry, I used SUMIFS instead of COUNTIFS. My bad.
My reasoning behind the Check Boxes was so that you can choose which columns include a batting score (I don't know what is in the other columns, or how often the batting scores repeat across a row.)
Formula in Header Cell B2 (and Fill Down)
=COUNTIFS(2:2,">49",2:2,"<100",$1:$1,"=TRUE")
Another way:
Formula in B2 (and Fill Down)
=COUNTIFS(2:2,">49",2:2,"<100",$1:$1,"=Batting Score")
If that does not work, please post a screen shot of the first few rows of your table.
Regards,
Ian.
-
Aug 23, 2016 7:31 AMby Ali Jennings,Hi Ian, You're being really helpful. Thank you. I can't get your formula to work so am going to try post screenshots (I'm new at this community forum thing!!) and try explain what I need. Rows 5 to 23 are players. Columns G, K, P, U, Z, etc are runs. In column GI I am trying to have a count of all scores between 49 and 100 and in column GH is a count of all scores >99. Managed to get my screenshots in below:
-
Aug 23, 2016 8:18 AMby Yellowbox,Hi Ali,
Thanks for the screen shots. Here are some suggestions (please don't be offended; we were all once new to these forums and new to Numbers.)
First some Numbers terminology:
- A spreadsheet in Numbers is called a document that can contain one or more Sheets.
- A Numbers Sheet can contain one or more tables or other objects.
- Command shift 3 captures the whole screen. The important bits get squashed and my old eyes can't read the details.
- Command shift 4 (and drag) captures a part of the screen; I suggest command shift 4 and drag just enough to show the top left of a table to show how the rows and columns "repeat".
- I could be wrong, but it appears that the table has some merged cells. Best to avoid merging; you can create the appearance of merged cells with formatting.
- Again, no offence, but the table looks like a final "Presentation" rather than a database from which to draw summaries.
- Numbers is designed to hold a database table (with simple formatting) from which several "reports" or "presentations" or "summaries" can be created, then moved to other sheets.
- Perhaps we should solve the COUNTIF/COUNTIFS problem before we move on to the reports.
Happy Numbering .
Regards,
Ian.
-
Aug 23, 2016 9:09 AMby t quinn,Hi Ali,
Like Ian, I am trying to figure out a better way to structure your Data table so that info can be easily extracted. The merged cells are killers. I cant tell how much of your 5 headers is really needed to identify a game. Is "Durham 256 & 223/3" a unique identifier? If so, you could have a much simpler table for your data entry like this:
Every game your be reported into this table. Your players could be in popups for ease of entry.
This would be a much easier table to extract data from. You could even add columns to count your over 100s and >49-<100s.
It would be then a simple operation to show that data in report tables.
quinn
