Product and Summation with Indexed Variable in Excel

0
votes

How can products and summations with indexed variables be done in Excel?

For example below, using the indexed values in A1:A5

   A  B  C
1  1
2  3
3  6
4  8
5  9

index

product

summation

I have been looking at the answers here, but they don't cover the indexed variable

Sigma or Summation function in Excel

Answers eschewing array formulas would be preferable. All welcome though.

Edit

Trying chris neilsen's adapted answer on Excel for Mac 2011, Windows Office 2010 and Mac Office 2019. All produce '4'.

enter image description here

but it does work on Office 365.

enter image description here

1
excelexcel-formula
why no array formulas? - teylyn
@teylyn I'd like to keep the solution as basic as possible, but if an array formula is the way, no problem. - Chris Degnen
@teylyn Dirk Reichel's one line formula here appealed, but I could not modify it to a product nor add an indexed variable. - Chris Degnen

1 Answers

2
votes

If you have Excel 365, the Dynamic Array functions can help here (this assumes that column A has only the data you want to process. However, the data doesn't have to start in row 1, and doesn't have to be contiguous)

Product

=LET(Data,FILTER(A:A,A:A<>""),PRODUCT(SEQUENCE(ROWS(Data))*3+Data))

Sum

=LET(Data,FILTER(A:A,A:A<>""),SUM(SEQUENCE(ROWS(Data))*3+Data))

enter image description here

If you don't have Excel 365, then the answer by Dick can be adapted. This does assume the data starts in row 1, and that the data is contiguous. It requires that cell B1 contains the count of values (which could be a formula)

Note: I don't have a non-365 version to test on, so can't be entirely sure these will work. You might have to enter them as an array formula (or use SUMPRODUCT for the sum version, can't offer an alternative for the PRODUCT version

=PRODUCT(3*(ROW(A1:INDEX(A:A,B1)))+A1:INDEX(A:A,B1))
=SUM(3*(ROW(A1:INDEX(A:A,B1)))+A1:INDEX(A:A,B1))

enter image description here Note: there is an error in that answer, corrected here