SUMIF formula on Filtered (or visible cells) - HELP

[Deano4iu]

I am attempting to have the functionality to be able to filter on the [BRAND] column and receive back the summed quantity of both Types (LEGACY and NPD).

#1 - I am using the subtotal formula above each month to sum the filtered months for that particular month.

I would now like to be able to filter on other columns and receive the total of the filter for the 2 TYPES listed below.



#2 - I am using the SUMIF formula to sum the different types of data provided in the TYPE column.

Notice that when I filter to the [CONVERSE] brand that the SUBTOTAL functionality works correctly but the SUMIF functionality does not ignore all the filtered fields as it keeps the same total even after filtering on the BRAND column.

Is it possible to make this happen??

 

[JamesCanale]

When there may be any kind of filtering, and I want the filtered subtotals, I use a helper column that just checks if the row is visible or not (using subtotal) and then sums up the column of interest times the helper column (which is 1 or 0).

MrExcelPlayground20.xlsx
	A	B	C	D	E	F
1	Name	Section	Visible	Sales		6200
2	Jamie	51	1	1000
3	Jamie	40	1	500
4	Harry	30	1	2000
5	Harry	45	1	600
6	Sally	25	1	2100
Sheet27

Cell Formulas
Range	Formula
F1	F1	=SUM(Table2[Sales]*Table2[Visible])
C2:C6	C2	=SUBTOTAL(102,B2)

 

[awoohaw]

Another set of options:


For your subtotal headers you can use the AGGREGATE function, and use the argument to ignore hidden rows.

The next part I am not entirely sure if your asking for filtered values in the LEGACY and NBD cells.
For the summary at the top you can use SUM function with an calculated array (entered by CNTL-SHFT-ENTR keystroke)
IF SUM does not work, replace it with SUMPRODUCT.

Book1
	A	B	C	D	E	F
1			BRAND	Converse
2			LEGACY	619
3			NBD	233
4
5				2323	2243
6	DESCRIPTION	BRAND	TYPE	2024-04-04	2024-05-01
7	Shoe 01	Nike	Legacy	68	53
8	Shoe 02	Adidas	NBD	52	56
9	Shoe 03	Puma	NBD	53	66
10	Shoe 04	Converse	NBD	54	59
11	Shoe 05	Under Armour	Legacy	51	71
12	Shoe 06	Nike	Legacy	60	56
13	Shoe 07	Adidas	Legacy	66	67
14	Shoe 08	Puma	NBD	52	51
15	Shoe 09	Converse	NBD	68	52
16	Shoe 10	Under Armour	Legacy	74	51
17	Shoe 11	Nike	Legacy	70	65
18	Shoe 12	Adidas	Legacy	55	55
19	Shoe 13	Puma	NBD	73	59
20	Shoe 14	Converse	Legacy	67	61
21	Shoe 15	Under Armour	Legacy	67	64
22	Shoe 16	Nike	Legacy	63	73
23	Shoe 17	Adidas	NBD	53	71
24	Shoe 18	Puma	Legacy	72	72
25	Shoe 19	Converse	Legacy	52	66
26	Shoe 20	Under Armour	NBD	60	61
27	Shoe 21	Nike	NBD	64	51
28	Shoe 22	Adidas	Legacy	71	54
29	Shoe 23	Puma	NBD	60	68
30	Shoe 24	Converse	Legacy	57	55
31	Shoe 25	Under Armour	Legacy	59	73
32	Shoe 26	Nike	NBD	58	70
33	Shoe 27	Adidas	NBD	51	63
34	Shoe 28	Puma	NBD	73	50
35	Shoe 29	Converse	Legacy	66	61
36	Shoe 30	Under Armour	NBD	71	50
37	Shoe 31	Nike	NBD	68	58
38	Shoe 32	Adidas	Legacy	73	58
39	Shoe 33	Puma	NBD	74	67
40	Shoe 34	Converse	Legacy	70	64
41	Shoe 35	Under Armour	Legacy	58	59
42	Shoe 36	Nike	NBD	50	52
43	Shoe 37	Adidas	NBD	70	61
44
Sheet3

Cell Formulas
Range	Formula
D2:D3	D2	=SUM(($B$7:$B$43=$D$1)*($C2=$C$7:$C$43)*(($D$7:$D$43)+($E$7:$E$43)))
D5:E5	D5	=AGGREGATE(9,5,D$7:D$43)
Press CTRL+SHIFT+ENTER to enter array formulas.