I currently use a SUBTOTAL function in Excel 365 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?
I have found a few questions and answers where SUMIF or SUMPRODUCT is used in place of SUBTOTAL, but I can't work out how to use those and still be able to flick between AVERAGE, SUM and COUNT.
