Sumproduct formula question (no VBA, please)

westc4 · Jun 3, 2022

Hello excel experts,

I have the following formula:

=SUMPRODUCT(SUBTOTAL(103,OFFSET($F$26,ROW($F$26:$F$999999)-ROW($F$26),0)),($A$26:$A$999999=$A21)*($B$26:$B$999999=$B$7))

The issue that I have run into is that each time I update my pivot table that uses this formula I run out of resources. I would like to remove the "$999999" and find the last visible cell with data in it. Is there a way to do this in the formula? I do not want to run it with VBA. The workbook is extremely large and has a number of different pivot tables all linked to one source table. I just need to get this formula less resource-intensive, please.

Thank you,
Cari

KRice · Jun 3, 2022

I'm not following this part of your formula:

Excel Formula:

SUBTOTAL(103,OFFSET($F$26,ROW($F$26:$F$999999)-ROW($F$26),0))

What do you want this to return as an argument for the SUMPRODUCT function? The 103 code will perform a count of the range described by the OFFSET. And the OFFSET function is being passed an array for the number of rows...did you intend for ROW($F$26:$F$999999)-ROW($F$26) to return an array or a single number (which would happen if ROWS were used)? I would consider avoiding OFFSET and perhaps construct a dynamic range...but I'm not sure how to advise without knowing more about what is supposed to be delivered by this part of the formula. And would column F be reasonable to determine the lowest extent of the data table?

westc4 · Jun 5, 2022

KRice said:
I'm not following this part of your formula:

Excel Formula:

SUBTOTAL(103,OFFSET($F$26,ROW($F$26:$F$999999)-ROW($F$26),0))

What do you want this to return as an argument for the SUMPRODUCT function? The 103 code will perform a count of the range described by the OFFSET. And the OFFSET function is being passed an array for the number of rows...did you intend for ROW($F$26:$F$999999)-ROW($F$26) to return an array or a single number (which would happen if ROWS were used)? I would consider avoiding OFFSET and perhaps construct a dynamic range...but I'm not sure how to advise without knowing more about what is supposed to be delivered by this part of the formula. And would column F be reasonable to determine the lowest extent of the data table?

The data is hard to explain. Please see the sheet.

Book1.xlsx

C

22

100% Over 60

Thank you.

KRice · Jun 5, 2022

There is nothing on the sheet...just a blank cell at C22.

westc4 · Jun 5, 2022

KRice said:
There is nothing on the sheet...just a blank cell at C22.

Book1.xlsx

A

B

C

D

E

2

U001

U021

3

FY22 11 MAR

4

FY22 12 APR

5

FY23 01 MAY

6

7

FY22 11 MAR

U001

0001258728

101%

227,509.06

8

FY22 11 MAR

U001

0002015529

100%

196,867.40

9

FY22 11 MAR

U001

0001934026

100%

151,490.98

10

FY22 12 APR

U001

0001134211

100%

139,958.90

11

FY22 12 APR

U001

0001593007

100%

138,212.70

12

FY22 12 APR

U001

0001109728

100%

81,800.00

13

FY23 01 MAY

U001

0001106652

100%

75,655.00

14

FY23 01 MAY

U001

0001984352

100%

65,945.00

15

FY23 01 MAY

U001

0002019671

100%

53,806.06

16

FY22 11 MAR

U021

0001973845

100%

4,713.82

17

FY22 11 MAR

U021

0001260448

100%

4,700.00

18

FY22 11 MAR

U021

0001108808

100%

4,492.43

19

FY22 11 MAR

U021

0001136476

100%

4,437.00

20

FY22 11 MAR

U021

0001769980

100%

4,282.56

21

FY22 11 MAR

U021

0001106312

100%

4,200.00

22

FY22 12 APR

U021

0001105892

100%

4,060.31

23

FY22 12 APR

U021

0001769874

100%

4,012.01

24

FY22 12 APR

U021

0001742500

100%

4,001.89

25

FY22 12 APR

U021

0001106343

100%

3,847.88

26

FY23 01 MAY

U021

0001174248

100%

3,600.00

27

FY23 01 MAY

U021

0001768982

119%

3,567.66

Sheet1

Cell Formulas
Range	Formula
B3:B5	B3	=SUMPRODUCT(SUBTOTAL(103,OFFSET($D$7,ROW($D$7:$D$999805)-ROW($D$7),0)),($A$7:$A$999805=$A3)*($B$7:$B$999805=$B$2))
C3:C5	C3	=SUMPRODUCT(SUBTOTAL(109,OFFSET($E$7,ROW($E$7:$E$999805)-ROW($E$7),0)),($A$7:$A$999805=$A3)*($B$7:$B$999805=$B$2))
D3:D5	D3	=SUMPRODUCT(SUBTOTAL(103,OFFSET($D$7,ROW($D$7:$D$999805)-ROW($D$7),0)),($A$7:$A$999805=$A3)*($B$7:$B$999805=$D$2))
E3:E5	E3	=SUMPRODUCT(SUBTOTAL(109,OFFSET($E$7,ROW($E$7:$E$999805)-ROW($E$7),0)),($A$7:$A$999805=$A3)*($B$7:$B$999805=$D$2))

KRice · Jun 5, 2022

Have a look at the formula in F3:G5 and let me know if they improve the efficiency of the sheet. SUMPRODUCT with long arrays can be slow. This approach creates dynamic arrays for the three ranges of interest. To do that, this part of the LET formula finds the last row in the worksheet based on looking down column E (in this example, it returns 27).

Excel Formula:

lrow, MAX((E:E<>"")*(ROW(E:E))),

This part finds the index of the lowest row in column E...so the row number of the top cell is subtracted from lrow and then 1 added so the index begins at 1 for the 1st data point:

Excel Formula:

bidx,  lrow-ROW($E$7)+1,

These parts create the dynamic ranges, so instead of the ranges being described by arrays that are 999993 elements long, the range will adapt to smaller sizes:

Excel Formula:

dynE, $E$7:INDEX($E$7:$E$999999,bidx),
dynA, $A$7:INDEX($A$7:$A$999999,bidx),
dynB, $B$7:INDEX($B$7:$B$999999,bidx),

Finally, this part will filter the dynamic E range by determining where there are matches in the dynA and dyn B ranges to the values in $A3 and $D$2, respectively...and then the elements in that filtered array are summed.

Excel Formula:

SUM(FILTER(dynE,(dynA=$A3)*(dynB=$D$2))))

The count formula is similar, except COUNT replaces SUM in the last part of the formula.

MrExcel_20220603.xlsx

A

B

C

D

E

F

G

1

2

U001

U021

3

FY22 11 MAR

157

2295614.27

4

FY22 12 APR

132

1792781.66

5

FY23 01 MAY

6

7

FY22 11 MAR

U001

1258728

1.006572858

227509.06

8

FY22 11 MAR

U001

2015529

1

196867.4

9

FY22 11 MAR

U001

1934026

1

151490.98

10

FY22 12 APR

U001

1134211

1

139958.9

11

FY22 12 APR

U001

1593007

1

138212.7

12

FY22 12 APR

U001

1109728

1

81800

13

FY23 01 MAY

U001

1106652

1

75655

14

FY23 01 MAY

U001

1984352

1

65945

15

FY23 01 MAY

U001

2019671

1

53806.06

16

FY22 11 MAR

U021

1973845

1

4713.82

17

FY22 11 MAR

U021

1260448

1

4700

18

FY22 11 MAR

U021

1108808

1

4492.43

19

FY22 11 MAR

U021

1136476

1

4437

20

FY22 11 MAR

U021

1769980

1

4282.56

21

FY22 11 MAR

U021

1106312

1

4200

22

FY22 12 APR

U021

1105892

1

4060.31

23

FY22 12 APR

U021

1769874

1

4012.01

24

FY22 12 APR

U021

1742500

1

4001.89

25

FY22 12 APR

U021

1106343

1

3847.88

26

FY23 01 MAY

U021

1174248

1

3600

27

FY23 01 MAY

U021

1768982

1.186191258

3567.66

Sheet11

Cell Formulas
Range	Formula
D3:D5	D3	=SUMPRODUCT(SUBTOTAL(103,OFFSET($D$7,ROW($D$7:$D$999805)-ROW($D$7),0)),($A$7:$A$999805=$A3)*($B$7:$B$999805=$D$2))
E3:E5	E3	=SUMPRODUCT(SUBTOTAL(109,OFFSET($E$7,ROW($E$7:$E$999805)-ROW($E$7),0)),($A$7:$A$999805=$A3)*($B$7:$B$999805=$D$2))
F3:F5	F3	=LET(lrow, MAX(($E:$E<>"")(ROW($E:$E))), bidx, lrow-ROW($E$7)+1, dynE, $E$7:INDEX($E$7:$E$999999,bidx), dynA, $A$7:INDEX($A$7:$A$999999,bidx), dynB, $B$7:INDEX($B$7:$B$999999,bidx), COUNT(FILTER(dynE,(dynA=$A3)(dynB=$D$2))))
G3:G5	G3	=LET(lrow, MAX(($E:$E<>"")(ROW($E:$E))), bidx, lrow-ROW($E$7)+1, dynE, $E$7:INDEX($E$7:$E$999999,bidx), dynA, $A$7:INDEX($A$7:$A$999999,bidx), dynB, $B$7:INDEX($B$7:$B$999999,bidx), SUM(FILTER(dynE,(dynA=$A3)(dynB=$D$2))))
B5	B5	=SUMPRODUCT(SUBTOTAL(103,OFFSET($D$7,ROW($D$7:$D$999805)-ROW($D$7),0)),($A$7:$A$999805=$A5)*($B$7:$B$999805=$B$2))
C5	C5	=SUMPRODUCT(SUBTOTAL(109,OFFSET($E$7,ROW($E$7:$E$999805)-ROW($E$7),0)),($A$7:$A$999805=$A5)*($B$7:$B$999805=$B$2))

KRice · Jun 6, 2022

You may want to use column A for determining the bottom row of data, as you appear to be using merged cells for column headings. Generally, that's a bad idea, as some formulas do not work correctly when they encounter a merged cell. I'd recommend leaving the heading in the first cell and then select it and the appropriate cells to its right and then Home > Alignment submenu (Format Cells), and then under horizontal, select Center Across Selection. Here is a version using column A for establishing the bottom row:

MrExcel_20220603.xlsx

A

B

C

D

E

1

2

U001

U021

3

FY22 11 MAR

4

FY22 12 APR

5

FY23 01 MAY

6

7

FY22 11 MAR

U001

1258728

1.006572858

227,509.06

8

FY22 11 MAR

U001

2015529

1

196,867.40

Sheet11 (2)

Cell Formulas
Range	Formula
B3:B5	B3	=LET(lrow, MAX(($A:$A<>"")(ROW($A:$A))), bidx, lrow-ROW($E$7)+1, dynE, $E$7:INDEX($E$7:$E$999999,bidx), dynA, $A$7:INDEX($A$7:$A$999999,bidx), dynB, $B$7:INDEX($B$7:$B$999999,bidx), COUNT(FILTER(dynE,(dynA=$A3)(dynB=$B$2))))
C3:C5	C3	=LET(lrow, MAX(($A:$A<>"")(ROW($A:$A))), bidx, lrow-ROW($E$7)+1, dynE, $E$7:INDEX($E$7:$E$999999,bidx), dynA, $A$7:INDEX($A$7:$A$999999,bidx), dynB, $B$7:INDEX($B$7:$B$999999,bidx), SUM(FILTER(dynE,(dynA=$A3)(dynB=$B$2))))
D3:D5	D3	=LET(lrow, MAX(($A:$A<>"")(ROW($A:$A))), bidx, lrow-ROW($E$7)+1, dynE, $E$7:INDEX($E$7:$E$999999,bidx), dynA, $A$7:INDEX($A$7:$A$999999,bidx), dynB, $B$7:INDEX($B$7:$B$999999,bidx), COUNT(FILTER(dynE,(dynA=$A3)(dynB=$D$2))))
E3:E5	E3	=LET(lrow, MAX(($A:$A<>"")(ROW($A:$A))), bidx, lrow-ROW($E$7)+1, dynE, $E$7:INDEX($E$7:$E$999999,bidx), dynA, $A$7:INDEX($A$7:$A$999999,bidx), dynB, $B$7:INDEX($B$7:$B$999999,bidx), SUM(FILTER(dynE,(dynA=$A3)(dynB=$D$2))))

westc4 · Jun 7, 2022

Thank you! This works, but calculates all cells, how would I calculate/count only the visible cells when the data is filtered?

KRice · Jun 7, 2022

Finally some clarity on the question I asked in Post #2 about the OFFSET formula and why it was being used like that inside the SUBTOTAL function...if all you really wanted to do is perform conditional counts and sums of values in a column. A long time ago, @Domenic described a similar formula construction in response to a problem involving the SUBTOTAL function and array formulas:

Array formulas - using Subtotal function?

Is it possible to use the subtotal function within an array formula? I've used array formulas for sum, count, average; and they work fine, but when I tried using subtotal, I get an error message. Does anyone know if this works or not?

www.mrexcel.com

The answer to my question is that you want to be able to manually filter your data and have the counts and sums produced by the formulas reflect the column autofilter selections. That's an important piece of information, and the solution I offered previously will not work under those conditions...as you've discovered. There aren't many formulas/functions that allow you to operate on cells that remain visible after manually hiding others. SUBTOTAL is one of the exceptions and is the reason why your initial formula used it. The OFFSET function works within the SUBTOTAL function to return an array of values that are to be counted or summed, subject to any conditionals, except values on any hidden/filtered rows will be replaced with 0. So the easiest solution is to revert back to a variation of the SUMPRODUCT/SUBTOTAL/OFFSET formula, except use dynamic arrays (yellow cells). Another variation to avoid SUMPRODUCT operating on long arrays is to use a SUM(IF construction (orange cells). I would try the latter first to see if it offers any performance improvements.

MrExcel_20220603.xlsx

A

B

C

D

E

F

G

H

I

1

2

U001

U021

3

FY22 11 MAR

4

FY22 12 APR

5

FY23 01 MAY

6

7

Date Desc

Code

Nbr1

Nbr2

Amt

8

FY22 11 MAR

U001

1258728

1.006572858

227,509.06

9

FY22 11 MAR

U001

2015529

1

196,867.40

10

FY22 11 MAR

U001

1934026

1

151,490.98

11

FY22 12 APR

U001

1134211

1

139,958.90

12

FY22 12 APR

U001

1593007

1

138,212.70

13

FY22 12 APR

U001

1109728

1

81,800.00

14

FY23 01 MAY

U001

1106652

1

75,655.00

15

FY23 01 MAY

U001

1984352

1

65,945.00

16

FY23 01 MAY

U001

2019671

1

53,806.06

19

FY22 11 MAR

U021

1108808

1

4,492.43

21

FY22 11 MAR

U021

1769980

1

4,282.56

23

FY22 12 APR

U021

1105892

1

4,060.31

24

FY22 12 APR

U021

1769874

1

4,012.01

26

FY22 12 APR

U021

1106343

1

3,847.88

27

FY23 01 MAY

U021

1174248

1

3,600.00

Sheet11 (2)

Cell Formulas
Range	Formula
B3:B5	B3	=LET(lrow, MAX(($A:$A<>"")(ROW($A:$A))), bidx, lrow-ROW($E$8)+1, dynE, $E$8:INDEX($E$8:$E$1000000,bidx), dynA, $A$8:INDEX($A$8:$A$1000000,bidx), dynB, $B$8:INDEX($B$8:$B$1000000,bidx), COUNT(FILTER(dynE,(dynA=$A3)(dynB=$B$2))))
C3:C5	C3	=LET(lrow, MAX(($A:$A<>"")(ROW($A:$A))), bidx, lrow-ROW($E$8)+1, dynE, $E$8:INDEX($E$8:$E$1000000,bidx), dynA, $A$8:INDEX($A$8:$A$1000000,bidx), dynB, $B$8:INDEX($B$8:$B$1000000,bidx), SUM(FILTER(dynE,(dynA=$A3)(dynB=$B$2))))
D3:D5	D3	=LET(lrow, MAX(($A:$A<>"")(ROW($A:$A))), bidx, lrow-ROW($E$8)+1, dynE, $E$8:INDEX($E$8:$E$1000000,bidx), dynA, $A$8:INDEX($A$8:$A$1000000,bidx), dynB, $B$8:INDEX($B$8:$B$1000000,bidx), COUNT(FILTER(dynE,(dynA=$A3)(dynB=$D$2))))
E3:E5	E3	=LET(lrow, MAX(($A:$A<>"")(ROW($A:$A))), bidx, lrow-ROW($E$8)+1, dynE, $E$8:INDEX($E$8:$E$1000000,bidx), dynA, $A$8:INDEX($A$8:$A$1000000,bidx), dynB, $B$8:INDEX($B$8:$B$1000000,bidx), SUM(FILTER(dynE,(dynA=$A3)(dynB=$D$2))))
F3:F5	F3	=LET(lrow, MAX(($A:$A<>"")(ROW($A:$A))), bidx, lrow-ROW($E$8)+1, dynE, $E$8:INDEX($E$8:$E$1000000,bidx), dynA, $A$8:INDEX($A$8:$A$1000000,bidx), dynB, $B$8:INDEX($B$8:$B$1000000,bidx), SUMPRODUCT(SUBTOTAL(103,OFFSET(dynE,ROW(dynE)-MIN(ROW(dynE)),0,1)),(dynA=$A3)(dynB=$D$2)))
G3:G5	G3	=LET(lrow, MAX(($A:$A<>"")(ROW($A:$A))), bidx, lrow-ROW($E$8)+1, dynE, $E$8:INDEX($E$8:$E$1000000,bidx), dynA, $A$8:INDEX($A$8:$A$1000000,bidx), dynB, $B$8:INDEX($B$8:$B$1000000,bidx), SUMPRODUCT(SUBTOTAL(109,OFFSET(dynE,ROW(dynE)-MIN(ROW(dynE)),0,1)),(dynA=$A3)(dynB=$D$2)))
H3:H5	H3	=LET(lrow, MAX(($A:$A<>"")(ROW($A:$A))), bidx, lrow-ROW($E$8)+1, dynE, $E$8:INDEX($E$8:$E$1000000,bidx), dynA, $A$8:INDEX($A$8:$A$1000000,bidx), dynB, $B$8:INDEX($B$8:$B$1000000,bidx), SUM(IF((dynA=$A3)(dynB=$D$2),SUBTOTAL(103,OFFSET(dynE,ROW(dynE)-MIN(ROW(dynE)),0,1)),"")))
I3:I5	I3	=LET(lrow, MAX(($A:$A<>"")(ROW($A:$A))), bidx, lrow-ROW($E$8)+1, dynE, $E$8:INDEX($E$8:$E$1000000,bidx), dynA, $A$8:INDEX($A$8:$A$1000000,bidx), dynB, $B$8:INDEX($B$8:$B$1000000,bidx), SUM(IF((dynA=$A3)(dynB=$D$2),SUBTOTAL(109,OFFSET(dynE,ROW(dynE)-MIN(ROW(dynE)),0,1)),"")))

Named Ranges
Name	Refers To	Cells
'Sheet11 (2)'!_FilterDatabase	='Sheet11 (2)'!$A$7:$E$28	B3:I5

Sumproduct formula question (no VBA, please)

westc4

Board Regular

Excel Facts

KRice

Well-known Member

westc4

Board Regular

KRice

Well-known Member

westc4

Board Regular

KRice

Well-known Member

KRice

Well-known Member

westc4

Board Regular

KRice

Well-known Member

Array formulas - using Subtotal function?

Similar threads

Forum statistics

Share this page

Sumproduct formula question (no VBA, please)

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Well-known Member

Board Regular

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock