EDIT: The document is set up like a series of dashboards. Some if the data linked to the many graphs is in pivot tables and some just regular tables. Because there are so many graphs, I use some user selection buttons and fields to change what data is shown in these graphs.
Some users open the file in office 2016.
All solutions I found were either limited to 365 or involved creating new data tables or columns which I was trying to avoid as it would mean a fair bit of rework. I instead just used a set of nested IFS and will eventually look at changing these particular pivots to regular tables with index lookups to enable the actual data to be in multiple columns.
I currently use a SUBTOTAL function to either sum, count or average a bunch of cells in a range. I was previously manually filtering the range so I was only totaling the rows I wanted, however the need has arisen to be able to look at several criteria at once. i.e in the example below, I was previously manually filtering range to only include "Apple" but now I need to be able to total "Apple", "Orange", "Banana" separately, at the same time.
The subtotal fields are used in graphs and I have a cell (F5) that houses a number corresponding to either SUM, COUNT or Average (9, 2 or 1) to use in the subtotal formulas in the "Summary table" which is linked to other functionality within the workbook and I need to still be able to retain that functionality.
Example of how my sheet is setup:
Raw Data
| Product Type | Sales QTY | Date |
|---|---|---|
| Apple | 4 | 1/9/21 |
| Orange | 3 | 6/9/21 |
| Banana | 2 | 10/9/21 |
| Apple | 6 | 14/9/21 |
| Orange | 6 | 20/9/21 |
| Apple | 5 | 29/9/21 |
The Criteria I want to match is in Column 1 (Product Type) of the summary table.
Basically, I then want to be able to end up with the ability to display the data either as Totals:
$F$5 = 9
for each line: SUBTOTAL($F$5,SalesQTY)
Summary Table
| Product Type | Result (Sales Per Month) |
|---|---|
| Apple | 15 |
| Orange | 9 |
| Banana | 2 |
Or as Averages:
$F$5 = 1
for each line: SUBTOTAL($F$5,SalesQTY)
| Product Type | Result (Average QTY per Sale) |
|---|---|
| Apple | 5 |
| Orange | 4.5 |
| Banana | 2 |
Or as a Count:
$F$5 = 2
for each line: SUBTOTAL($F$5,SalesQTY)
| Product Type | Result (# Sales Transactions) |
|---|---|
| Apple | 2 |
| Orange | 2 |
| Banana | 1 |
Is there some way I can combine SUMIF and also SUBTOTAL but also be able to retain the ability to flick between average, sum and count?
Solution Used:
To avoid having to rework the many other tables in the sheet that rely on this field and also as some user open this file with older (non-365) versions of excel, I opted for a series of nested IF (CountIF, SumIF, AverageIF) statements instead.