I want to add sub-total columns to count even/odd numbers per rows or columns. broken down by multiple ranges. EX: 90 total numbers. How many are Even 1-6 or Odd 1-6, then Even 7-12 or Odd 7-12....etc to have these sub-totals for all ninety entries. I have grand totals for Month/Year and each individual number, but the sub-totals would be more useful.
MacBook Air 13″, macOS 11.7
Not sure that can be done with a Pivot Table, as my explorations haven't entered that neighbourhood. Not too difficult using formulas, though.
You've given no information regarding the range of the individual numbers, so I've used the values possible on a throw of a single die (1-6), and arranged the 90 numbers in rows of 6, matching the quantity in your description.
Above is the table as displayed.
Columns H to M are hidden to keep the visual size of the table relatively small and to reduce the probability of accidentally damaging the formula in these cells. As seen below, though, the cells in these hidden columns contain the key formula that determines if each cell in columns B to G contains an Even or an Odd number, then expresses the result with the letters E or O.
The formulas in columns N and O use COUNTIF to count the number of Os or Es in their row:
N2: COUNTIF($H2:$M2,"O")
Fill right to O2
Then select both cells and fill down to Row 16
The bottom row of the table (17) is a Footer Row. The SUM formulas in columns N and O add the numbers in their column to display the total number of Odd and Even numbers in the respective columns.
N17: SUM(N2:N16)
O17: SUM(O2:O16)
For the example, I used this formula, entered in B2, then filled down and right to G16:
B2: =RANDBETWEEN(1,6)
In your case, you'll be entering observed data, rather than creating sample 'data' with a formula.
Regards,
Barry
Thanks Barry! I truly appreciate your time and detail! I actually have the data, on several individual spreadsheets, with massive data and columns to break everything down, using the formulas. Using a pivot table for only select columns/rows/items I do not need all the other detail or formulas. It just returns the results of a what I selected in a way, that is instantly refreshed and not having all the detail of the larger spreadsheet. I add columns to return TRUE/ FALSE at the end of my spreadsheet rows. All data typed, across several spreadsheets consists of a date and 6 number entries Then in various columns it is just the formula result accurately displayed ISEVEN(Cell or Range) and COUNTIFS($E2:$I2),">=1",($E2:$I2),">=17",($AJ2:$AN2),"=TRUE") Several spreadsheets are updated to break down into categories, that I need, from the entire data set. Having the ability with a Pivot Table to have grand and subtotals, for only a few pieces of the existing data, that can EasilyQuickly be a refreshed snapshot, would be so wonderful and time saving.
It would help a lot if you could post as screenshot. shift-command-4, select area with cross-hairs, release, start new post here and use the mountains-and-moon 'Image insertion' icon to include the screenshot from the Desktop.
SG
Pivot table help for sub totals
