Formula in one column to calculate the sumproduct of cells between two blank cells

secrestj · Jan 27, 2025

I previously posted a similar question; however, I did not provide all the details and intended to adapt any response to my need. I was UNSUCESSFUL, being 'to smart by half'.

The following table represents real estate type and their statuses, with their quantity and occupancy. The 'Total' rows (6,10,15,20 - in the sample below) represent the weighted average in the percentage column.

I want to create a formula in column E that allows me to copy/paste new data in columns A, B, C, D and columns E will adjust to the new quantity of the data sets (with the format and columns A, B, C, D being the same). For example, sometimes the quantity of multi-family data set has 2, 4, 5 rows rather than 3 rows in this example below. The goes with 'office' and sometimes there is 'retail (or other real estate types).
As such, I am trying for a formula that is already in Column E (adjusting to the change in Column A-D data) and I can drag the formula down if there are more rows in the data set. Also, I am validating the weighted average formula is calculating correctly and not relying on the data totals in columns C and D that are provided to me.

My current formula (before considering your assistance) is the following, but it is not dynamic to grow/contract with the quantity (row) changes of MF, Office, etc. and I go into the formula and add/reduce rows in the formulas on each of the total rows in column E.

E6	=IF(ABS(SUMPRODUCT(C3:C5,D3:D5)/C6-D6)>0.01,"FAIL","VALID")

Then, I started with a formula that only displays calculations in Column E based on an if statement, but cannot figure out the how to make a formula that runs the calculation based on the rows between the two 'blank' cells of column B, which is the only consistent pattern I can think of to base the dynamic calculations and make it drag-able down column E if there is more data provided.

E6	=IF(OR(AND(ISTEXT($A6),$B6=""),AND($A6="",$B6="")),"", ???????????????????

	A	B	C	D	E
1	Type	Status	Amount	%	Valid/Fail
2	Commercial RE
3	MF-1	Good	20	95.0%
4	MF-2	Good	40	88.0%
5	MF-3	Good	30	92.0%
6	Total Good MF		90	90.9%	VALID
7	MF-1	Bad	20	82.0%
8	MF-2	Bad	10	84.0%
9	MF-3	Bad	10	73.0%
10	Total Bad MF		40	80.3%	VALID
11	Office-1	Good	50	94.0%
12	Office-2	Good	10	94.0%
13	Office-3	Good	30	91.0%
14	Office-4	Good	40	92.0%
15	Total Good Office		130	92.7%	VALID
16	Office-1	Bad	10	71.0%
17	Office-2	Bad	10	83.0%
18	Office-3	Bad	20	78.0%
19	Office-4	Bad	50	77.0%
20	Total Bad Office		90	77.2%	VALID

If you can help figure this formula out, I would greatly appreciate it. This forum has always been an amazing resource. Thank you.

coulombevin · Jan 27, 2025

Hi secrestj,

First of all your answer should be fail since your division is not interpreted like it should. What you do is SUMPROD by C6 then minus D6, instead of SUMPROD divided by the difference between C6 and D6.

Here's what I would do (formula for cell E2 then drag down):

Excel Formula:

=IF(B2="",LET(nextEmptyRow,CHOOSEROWS(LAMBDA(c,FILTER(c,($A:$A<>"")*(c<>"")*(c>ROW()),""))(BYROW($B:$B,LAMBDA(r,IF(r<>"","",ROW(r))))),1),tbl,OFFSET($C3,0,0,MAX(nextEmptyRow)-ROW(),2),nRow,ROWS(tbl),sumProdVal,SUMPRODUCT(OFFSET(tbl,0,0,nRow-1,1),CHOOSECOLS(OFFSET(tbl,0,0,nRow-1,2),2)),divVal,CHOOSECOLS(CHOOSEROWS(tbl,nRow),1)-CHOOSECOLS(CHOOSEROWS(tbl,nRow),2),IF(ABS(sumProdVal/divVal)>0.01,"FAIL","VALID")),"")

See in this example:

Classeur1

A

B

C

D

E

1

Type

Status

Amount

%

Valid/Fail

2

Commercial RE

FAIL

3

MF-1

Good

20

95,00%

4

MF-2

Good

40

88,00%

5

MF-3

Good

30

92,00%

6

Total Good MF

90

90,90%

FAIL

7

MF-1

Bad

20

82,00%

8

MF-2

Bad

10

84,00%

9

MF-3

Bad

10

73,00%

10

Total Bad MF

40

80,30%

FAIL

11

Office-1

Good

50

94,00%

12

Office-2

Good

10

94,00%

13

Office-3

Good

30

91,00%

14

Office-4

Good

40

92,00%

15

Total Good Office

130

92,70%

FAIL

16

Office-1

Bad

10

71,00%

17

Office-2

Bad

10

83,00%

18

Office-3

Bad

20

78,00%

19

Office-4

Bad

50

77,00%

Feuil1

Cell Formulas
Range	Formula
E2:E19	E2	=IF(B2="",LET( nextEmptyRow,CHOOSEROWS(LAMBDA(c,FILTER(c,($A:$A<>"")(c<>"")(c>ROW()),""))(BYROW($B:$B,LAMBDA(r,IF(r<>"","",ROW(r))))),1), tbl,OFFSET($C3,0,0,MAX(nextEmptyRow)-ROW(),2), nRow,ROWS(tbl), sumProdVal,SUMPRODUCT(OFFSET(tbl,0,0,nRow-1,1),CHOOSECOLS(OFFSET(tbl,0,0,nRow-1,2),2)), divVal,CHOOSECOLS(CHOOSEROWS(tbl,nRow),1)-CHOOSECOLS(CHOOSEROWS(tbl,nRow),2), IF(ABS(sumProdVal/divVal)>0.01,"FAIL","VALID") ),"")

Bests regards,

Vincent

secrestj · Feb 10, 2025

Thank you for your response; however, the original formula was correct. The formula tests the weighted average calculation is performed correctly, allowing for a 1% variance. The formula typically returns a 0, which is 'Valid'. Any result greater than 1% results in a 'Fail'.
I have been unsuccessful adapting your formula - would you consider modifying it to consider my additional comment?

coulombevin · Feb 10, 2025

secrestj said:
Thank you for your response; however, the original formula was correct. The formula tests the weighted average calculation is performed correctly, allowing for a 1% variance. The formula typically returns a 0, which is 'Valid'. Any result greater than 1% results in a 'Fail'.
I have been unsuccessful adapting your formula - would you consider modifying it to consider my additional comment?

Hi secrestj,

Here's a modified formula. Take note that I provided an alternative formula more simple using DROP.

Classeur1

A

B

C

D

E

F

1

Type

Status

Amount

%

Valid/Fail

Valid/Fail_V2

2

Commercial RE

VALID

3

MF-1

Good

20

95%

4

MF-2

Good

40

88%

5

MF-3

Good

30

92%

6

Total Good MF

90

91%

VALID

7

MF-1

Bad

20

82%

8

MF-2

Bad

10

84%

9

MF-3

Bad

10

73%

10

Total Bad MF

40

80%

VALID

11

Office-1

Good

50

94%

12

Office-2

Good

10

94%

13

Office-3

Good

30

91%

14

Office-4

Good

40

92%

15

Total Good Office

130

93%

FAIL

16

Office-1

Bad

10

71%

17

Office-2

Bad

10

83%

18

Office-3

Bad

20

78%

19

Office-4

Bad

50

77%

Feuil1

Cell Formulas
Range	Formula
E2:E19	E2	=IF(B2="",LET( nextEmptyRow,CHOOSEROWS(LAMBDA(c,FILTER(c,($A:$A<>"")(c<>"")(c>ROW()),""))(BYROW($B:$B,LAMBDA(r,IF(r<>"","",ROW(r))))),1), tbl,OFFSET($C3,0,0,MAX(nextEmptyRow)-ROW(),2), nRow,ROWS(tbl), sumProdVal,SUMPRODUCT(OFFSET(tbl,0,0,nRow-1,1),CHOOSECOLS(OFFSET(tbl,0,0,nRow-1,2),2)), IF(ABS(sumProdVal/CHOOSECOLS(CHOOSEROWS(tbl,nRow),1)-CHOOSECOLS(CHOOSEROWS(tbl,nRow),2))>0.01,"FAIL","VALID") ),"")
F2:F19	F2	=IF(B2="",LET( nextEmptyRow,CHOOSEROWS(LAMBDA(c,FILTER(c,($A:$A<>"")(c<>"")(c>ROW()),""))(BYROW($B:$B,LAMBDA(r,IF(r<>"","",ROW(r))))),1), tbl,OFFSET($C3,0,0,MAX(nextEmptyRow)-ROW(),2), nRow,ROWS(tbl), sumProdVal,SUMPRODUCT(DROP(tbl,-1,-1),DROP(tbl,-1,1)), IF(ABS(sumProdVal/CHOOSECOLS(CHOOSEROWS(tbl,nRow),1)-CHOOSECOLS(CHOOSEROWS(tbl,nRow),2))>0.01,"FAIL","VALID") ),"")

Bests regards,

Vincent

Cubist · Feb 10, 2025

See if this works for you.

Book4

A

B

C

D

E

1

Type

Status

Amount

%

Valid/Fail

2

Commercial RE

3

MF-1

Good

20

95.00%

4

MF-2

Good

40

88.00%

5

MF-3

Good

30

92.00%

6

Total Good MF

90

90.90%

VALID

7

MF-1

Bad

20

82.00%

8

MF-2

Bad

10

84.00%

9

MF-3

Bad

10

73.00%

10

Total Bad MF

40

80.30%

VALID

11

Office-1

Good

50

94.00%

12

Office-2

Good

10

94.00%

13

Office-3

Good

30

91.00%

14

Office-4

Good

40

92.00%

15

Total Good Office

130

92.70%

VALID

16

Office-1

Bad

10

71.00%

17

Office-2

Bad

10

83.00%

18

Office-3

Bad

20

78.00%

19

Office-4

Bad

50

77.00%

20

Total Bad Office

90

77.20%

VALID

Sheet1

Cell Formulas
Range	Formula
E3:E20	E3	=IF(B3<>"","",LET(d,DROP(C$2:D2,XMATCH(TRUE,B$2:B2="",,-1)),IF(ABS(D3-SUM(INDEX(d,,1)*INDEX(d,,2)/SUM(INDEX(d,,1))))>1%,"FAIL","VALID")))

coulombevin · Feb 10, 2025

coulombevin said:
Hi secrestj,

Here's a modified formula. Take note that I provided an alternative formula more simple using DROP.

Classeur1
A B C D E F
1 Type Status Amount % Valid/Fail Valid/Fail_V2
2 Commercial RE VALID VALID
3 MF-1 Good 20 95%
4 MF-2 Good 40 88%
5 MF-3 Good 30 92%
6 Total Good MF 90 91% VALID VALID
7 MF-1 Bad 20 82%
8 MF-2 Bad 10 84%
9 MF-3 Bad 10 73%
10 Total Bad MF 40 80% VALID VALID
11 Office-1 Good 50 94%
12 Office-2 Good 10 94%
13 Office-3 Good 30 91%
14 Office-4 Good 40 92%
15 Total Good Office 130 93% FAIL FAIL
16 Office-1 Bad 10 71%
17 Office-2 Bad 10 83%
18 Office-3 Bad 20 78%
19 Office-4 Bad 50 77%
Feuil1
Cell Formulas
Range Formula
E2:E19 E2 =IF(B2="",LET( nextEmptyRow,CHOOSEROWS(LAMBDA(c,FILTER(c,($A:$A<>"")*(c<>"")*(c>ROW()),""))(BYROW($B:$B,LAMBDA(r,IF(r<>"","",ROW(r))))),1), tbl,OFFSET($C3,0,0,MAX(nextEmptyRow)-ROW(),2), nRow,ROWS(tbl), sumProdVal,SUMPRODUCT(OFFSET(tbl,0,0,nRow-1,1),CHOOSECOLS(OFFSET(tbl,0,0,nRow-1,2),2)), IF(ABS(sumProdVal/CHOOSECOLS(CHOOSEROWS(tbl,nRow),1)-CHOOSECOLS(CHOOSEROWS(tbl,nRow),2))>0.01,"FAIL","VALID") ),"")
F2:F19 F2 =IF(B2="",LET( nextEmptyRow,CHOOSEROWS(LAMBDA(c,FILTER(c,($A:$A<>"")*(c<>"")*(c>ROW()),""))(BYROW($B:$B,LAMBDA(r,IF(r<>"","",ROW(r))))),1), tbl,OFFSET($C3,0,0,MAX(nextEmptyRow)-ROW(),2), nRow,ROWS(tbl), sumProdVal,SUMPRODUCT(DROP(tbl,-1,-1),DROP(tbl,-1,1)), IF(ABS(sumProdVal/CHOOSECOLS(CHOOSEROWS(tbl,nRow),1)-CHOOSECOLS(CHOOSEROWS(tbl,nRow),2))>0.01,"FAIL","VALID") ),"")

Bests regards,

Vincent

Hi secrestj,

In my previous attempt I forgot the las row in your example, leading to a false Fail. Here a corrected function to prevent division by 0:

Classeur1

A

B

C

D

E

1

Type

Status

Amount

%

Valid/Fail_V2

2

Commercial RE

VALID

3

MF-1

Good

20

95%

4

MF-2

Good

40

88%

5

MF-3

Good

30

92%

6

Total Good MF

90

91%

VALID

7

MF-1

Bad

20

82%

8

MF-2

Bad

10

84%

9

MF-3

Bad

10

73%

10

Total Bad MF

40

80%

VALID

11

Office-1

Good

50

94%

12

Office-2

Good

10

94%

13

Office-3

Good

30

91%

14

Office-4

Good

40

92%

15

Total Good Office

130

93%

VALID

16

Office-1

Bad

10

71%

17

Office-2

Bad

10

83%

18

Office-3

Bad

20

78%

19

Office-4

Bad

50

77%

20

Total Bad Office

90

0,772

Feuil1

Cell Formulas
Range	Formula
E2:E19	E2	=IF(B2="",LET( nextEmptyRow,CHOOSEROWS(LAMBDA(c,FILTER(c,($A:$A<>"")(c<>"")(c>ROW()),ROWS(FILTER(c,($A:$A<>""),""))))(BYROW($B:$B,LAMBDA(r,IF(r<>"","",ROW(r))))),1), tbl,OFFSET($C3,0,0,MAX(nextEmptyRow)-ROW(),2), nRow,ROWS(tbl), sumProdVal,SUMPRODUCT(DROP(tbl,-1,-1),DROP(tbl,-1,1)), IF(ABS(sumProdVal/LAMBDA(div,IF(div=0,1,div))(CHOOSECOLS(CHOOSEROWS(tbl,nRow),1))-CHOOSECOLS(CHOOSEROWS(tbl,nRow),2))>0.01,"FAIL","VALID") ),"")

Bests regards,

Vincent

Sam_D_Ben · Feb 11, 2025

Another simple approach,

Sumproduct complex.xlsx

A

B

C

D

E

1

Type

Status

Amount

%

Valid/Fail

2

Commercial RE

-

3

MF-1

Good

20

95.00%

4

MF-2

Good

40

88.00%

5

MF-3

Good

30

92.00%

6

Total Good MF

90

90.90%

VALID

7

MF-1

Bad

20

82.00%

8

MF-2

Bad

10

84.00%

9

MF-3

Bad

10

73.00%

10

Total Bad MF

40

80.30%

VALID

11

Office-1

Good

50

94.00%

12

Office-2

Good

10

94.00%

13

Office-3

Good

30

91.00%

14

Office-4

Good

40

92.00%

15

Total Good Office

130

92.70%

VALID

16

Office-1

Bad

10

71.00%

17

Office-2

Bad

10

83.00%

18

Office-3

Bad

20

78.00%

19

Office-4

Bad

50

77.00%

20

Total Bad Office

90

77.20%

VALID

Sheet1

Cell Formulas
Range	Formula
E3:E20	E3	=IF(B3="",LET(nextEmptyRow, MIN(IF(($A:$A<>"")*($B:$B=""), ROW($B:$B))),tbl,OFFSET(C3,0,0,nextEmptyRow-ROW(D3),2),nRow,ROWS(tbl),sumProdVal, SUMPRODUCT(INDEX(tbl, SEQUENCE(nRow-1), 1), INDEX(tbl, SEQUENCE(nRow-1), 2)),lastRowVal1,INDEX(tbl, nRow, 1),lastRowVal2, INDEX(tbl, nRow, 2),IF(ABS((sumProdVal/(lastRowVal1))/(lastRowVal2))/(lastRowVal1)-(lastRowVal2)>0.01, "FAIL", "VALID")),"")

Sam_D_Ben · Feb 11, 2025

Sam_D_Ben said:
Another simple approach,

Sumproduct complex.xlsx
A B C D E
1 Type Status Amount % Valid/Fail
2 Commercial RE - -
3 MF-1 Good 20 95.00%
4 MF-2 Good 40 88.00%
5 MF-3 Good 30 92.00%
6 Total Good MF 90 90.90% VALID
7 MF-1 Bad 20 82.00%
8 MF-2 Bad 10 84.00%
9 MF-3 Bad 10 73.00%
10 Total Bad MF 40 80.30% VALID
11 Office-1 Good 50 94.00%
12 Office-2 Good 10 94.00%
13 Office-3 Good 30 91.00%
14 Office-4 Good 40 92.00%
15 Total Good Office 130 92.70% VALID
16 Office-1 Bad 10 71.00%
17 Office-2 Bad 10 83.00%
18 Office-3 Bad 20 78.00%
19 Office-4 Bad 50 77.00%
20 Total Bad Office 90 77.20% VALID
Sheet1
Cell Formulas
Range Formula
E3:E20 E3 =IF(B3="",LET(nextEmptyRow, MIN(IF(($A:$A<>"")*($B:$B=""), ROW($B:$B))),tbl,OFFSET(C3,0,0,nextEmptyRow-ROW(D3),2),nRow,ROWS(tbl),sumProdVal, SUMPRODUCT(INDEX(tbl, SEQUENCE(nRow-1), 1), INDEX(tbl, SEQUENCE(nRow-1), 2)),lastRowVal1,INDEX(tbl, nRow, 1),lastRowVal2, INDEX(tbl, nRow, 2),IF(ABS((sumProdVal/(lastRowVal1))/(lastRowVal2))/(lastRowVal1)-(lastRowVal2)>0.01, "FAIL", "VALID")),"")

PLEASE IGNORE IT, WRONG

Sam_D_Ben · Feb 12, 2025

This was quite interesting to me and i spent time to get a solution on my Excel 2021regular.
#sumproduct_of_cellrange_between_varying_emptycells

Sumproduct complex.xlsx

A

B

C

D

E

1

Type

Status

Amount

%

Valid/Fail

2

Commercial RE

3

MF-1

Good

20

95.00%

4

MF-2

Good

40

88.00%

5

MF-3

Good

30

92.00%

6

Total Good MF

90

90.90%

VALID

7

MF-1

Bad

20

82.00%

8

MF-2

Bad

10

84.00%

9

MF-3

Bad

10

73.00%

10

Total Bad MF

40

80.30%

VALID

11

Office-1

Good

50

94.00%

12

Office-2

Good

10

94.00%

13

Office-3

Good

30

91.00%

14

Office-4

Good

40

92.00%

15

Total Good Office

130

92.70%

VALID

16

Office-1

Bad

10

71.00%

17

Office-2

Bad

10

83.00%

18

Office-3

Bad

20

78.00%

19

Office-4

Bad

50

77.00%

20

Total Bad Office

90

77.20%

VALID

Sheet1

Cell Formulas
Range	Formula
E3:E20	E3	=IF(LEFT(A3,5)="Total",LET(currRow,ROW(),prevEmpty,IFERROR(MAX(IF($B$3:INDEX($B:$B,currRow-1)="",ROW($B$3:INDEX($B:$B,currRow-1)))),2),sumProdVal,SUMPRODUCT(INDEX(C:C,prevEmpty+1):INDEX(C:C,currRow-1),INDEX(D:D,prevEmpty+1):INDEX(D:D,currRow-1)),IF(ABS(sumProdVal/INDEX(C:C,currRow)-INDEX(D:D,currRow))>0.01,"FAIL","VALID")),"")

VBA Code:

Sub ValidateTotals()
 Dim ws As Worksheet: Set ws = ActiveSheet
 Dim lr As Long, i As Long, j As Long, k As Long, sp As Double, ta As Double, tp As Double, sb As Long, pe As Long
 lr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
 For i = 3 To lr
  If ws.Cells(i, "A") Like "Total*" Then
   pe = 0
   For j = i - 1 To 3 Step -1
    If ws.Cells(j, "B") = "" Then pe = j: Exit For
   Next j
   sb = IIf(pe = 0, 3, pe + 1)
   sp = 0: For k = sb To i - 1: sp = sp + ws.Cells(k, "C") * ws.Cells(k, "D"): Next k
   ta = ws.Cells(i, "C"): tp = ws.Cells(i, "D")
   ws.Cells(i, "E") = IIf(ta <> 0 And Abs(sp / ta - tp) <= 0.01, "VALID", "FAIL")
  End If
 Next i
End Sub

secrestj · Feb 13, 2025

To everyone the assisted me in the project (coulombevin, cubist, Sam_D_Ben) - I have been working on this project (off and on) for the past 3 days. I was able to solve it yesterday. I tried each of your recommendations (running Sam's most recent one this morning and THANK YOU (with the exception of Sam's VBA - being less familiar with VBA - but intend to do further research and attempt to apply later today/tomorrow to learn. I pulled from each example yesterday morning and came up with the following:

Cell Formulas
Range	Formula
E3:E20	E6	=IF(AND($B5="",$B6=""),"", IF(AND($B6="",ISNUMBER($C6)), LET(LastBlankRow,IFERROR(LOOKUP(2,1/($B$1:$B5=""),ROW($B$1:$B5)),0), StartRow, LastBlankRow+1, ValueRange, INDEX($D$1:D5, StartRow):D5, WeightRange, INDEX($C$1:$C5, StartRow):$C5, WeightedSum, SUMPRODUCT(ValueRange,WeightRange), TotalWeight, SUM(WeightRange), IF(TotalWeight<>0,IF((WeightedSum/TotalWeight-D6)>0.01,"FAIL","VALID"),"-")),""))

Prior to this project, I had never used LET, but your suggestions put me on that path, which I started researching and reminded me of algebra. Took a while to put it together, but it worked. As the validation table has many different (but similar) validations, this formula allowed me to easily alter the formula for each column. Plus, as Sam alluded... the challenge was fun. Again, THANK YOU for your help, as it led me to my solution and taught me a lot. JS

Formula in one column to calculate the sumproduct of cells between two blank cells

secrestj

New Member

coulombevin

Board Regular

secrestj

New Member

coulombevin

Board Regular

Cubist

Well-known Member

coulombevin

Board Regular

Sam_D_Ben

Well-known Member

Sam_D_Ben

Well-known Member

Sam_D_Ben

Well-known Member

secrestj

New Member

Similar threads

Forum statistics

Share this page

Formula in one column to calculate the sumproduct of cells between two blank cells

New Member

Board Regular

New Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Well-known Member

Well-known Member

New 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