SUBTOTAL, IF?

[Texas Longhorn]

Is it possible to combine a subtotal into a sumif? Example: I have a large table of data in 10 columns, all with column headings. In the same sheet, in 20 rows above the table, I have summary boxes where I return values based on parameters (eg, what is license revenue for deals rated "A").

Further, I have an autofilter on the table. Now, when I filter, say for quarter 4 only, I want my summary boxes to update like a subtotal does (eg. ignores hidden rows). I tried SUMIF, but it includes rows hidden by the filter.

Any ideas?

 

[PaddyD]

this can be done using a combination of sumproduct, subtotal & offset. an example is here:

http://www.mrexcel.com/board2/viewtopic.php?t=63012

...post back if you need more.

 

[Texas Longhorn]

Those are quite cool, but I'm not certain they solve my problem (maybe they do -- I'm pretty novice). I actually want the sum of all filtered rows, not just the first "x". However, I want the subtotal of column B (revenue) if Column A (Region) = South. It's a SUMIF, except I need it to update when I filter on column C (Quarter).

Thanks.

 

[PaddyD]

rte-create the following:

Book2
	A	B	C	D	E
1				Region	South
2				Sum	26
3	Region	Revenue	Quarter
4	North	1	1
5	South	2	2
6	East	3	1
7	North	4	2
8	South	5	1
9	East	6	2
10	North	7	1
11	South	8	2
12	East	9	1
13	North	10	2
14	South	11	1
Sheet1

the formula in e2 is:

=SUMPRODUCT((SUBTOTAL(3,OFFSET(A4:A14,ROW(A4:A14)-MIN(ROW(A4:A14)),,1)))*((A4:A14)=E1)*(B4:B14))

...which needs to be entered with conrtol + shift + enter, not just enter.

Filter the data to show only quarter 1 - the sum for the south region is 16.

 

[Mark W.]

Filter the list shown in rows 1:12 (below) based on 'Fruit' or 'Month' values and notice how the Salesman summary is affected...

Book1
	A	B	C	D	E	F	G	H
1	Fruit	Month	Salesperson	Qty	Field6	Field7
2	Apples	Feb	Larry	1	1	TRUE
3	Oranges	Mar	Larry	20	2	TRUE
4	Bananas	Feb	Moe	13	3	TRUE
5	Grapes	Feb	Curly	16	4	TRUE
6	Lemons	Jan	Curly	8	5	TRUE
7	Apples	Mar	Larry	2	6	TRUE
8	Oranges	Mar	Moe	18	7	TRUE
9	Bananas	Jan	Moe	17	8	TRUE
10	Grapes	Mar	Larry	7	9	TRUE
11	Lemons	Feb	Curly	6	10	TRUE
12				108
13
14			Salesperson	Qty
15			Larry	30
16			Moe	48
17			Curly	30
18				108
19
Sheet1

 

[Texas Longhorn]

Thanks--the formulas work great. That being said, I used PaddyD's formula, which does exactly what I need, but I have no idea what I told Excel to do (the more I'm here, the more I realize I define amateur). Could someone explain what the formula is doing?

Thanks.

 

[Aladin Akyurek]

Thanks--the formulas work great. That being said, I used PaddyD's formula, which does exactly what I need, but I have no idea what I told Excel to do (the more I'm here, the more I realize I define amateur). Could someone explain what the formula is doing?

Thanks.

See for SumProduct:

http://216.92.17.166/board2/viewtopic.php?t=59063

In

=SUMPRODUCT((SUBTOTAL(3,OFFSET(A4:A14,ROW(A4:A14)-MIN(ROW(A4:A14)),,1)))*((A4:A14)=E1)*(B4:B14))

the SUBTOTAL bit established which rows are visible (invented by Longre).

The conditional bit...

(A4:A14)=E1

establishes the A-cells that are equal to E1.

Algebraic And'ing of the result of the SubTotal bit, the conditional bit, and the range to sum (B1:B4) gives the desired total.

 

[Texas Longhorn]

Aladin, thanks. I enjoyed your sumproduct explanation. I am still lost on the OFFSET part of the formula, particularly the ROW(A4:A14)-MIN(ROW(A4:A14)),,1 part.

Sorry for all the questions.

Thanks.

 

[Aladin Akyurek]

Aladin, thanks. I enjoyed your sumproduct explanation. I am still lost on the OFFSET part of the formula, particularly the ROW(A4:A14)-MIN(ROW(A4:A14)),,1 part.

Sorry for all the questions.

Thanks.

The ROW(A4:A14)-MIN(ROW(A4:A14)) bit, when evaluated (check with F9 on the formula bar), gives:

{0,1,2,3} [ MIN helps to get 0 that is needed in order to include the start cell ]

an array each member of which specifies the number of rows from A1 (A1:A4 is read off by OFFSET as A1). A constant array as a rows specifier in OFFSET makes the formula possible. SUBTOTAL applies COUNTA (3) to a set of single-cell ranges. Since SUBTOTAL ignores invisible cells, we get an array of 1's and 0's (1 for each visible cell, 0 for each invisble cell).

 

[Tenacity]

The ROW(A4:A14)-MIN(ROW(A4:A14)) bit, when evaluated (check with F9 on the formula bar), gives:

{0,1,2,3} [ MIN helps to get 0 that is needed in order to include the start cell ]

an array each member of which specifies the number of rows from A1 (A1:A4 is read off by OFFSET as A1). A constant array as a rows specifier in OFFSET makes the formula possible. SUBTOTAL applies COUNTA (3) to a set of single-cell ranges. Since SUBTOTAL ignores invisible cells, we get an array of 1's and 0's (1 for each visible cell, 0 for each invisble cell).

Absolutely phenomenal!! Aladin loved your explanation of the array. Generating a series of 1's and 0's - ingenius!