Google Sheets – Using SUMPRODUCT Inside ArrayFormula

formulasgoogle sheetsgoogle-sheets-arrayformula

I have a fixed vector, say $A$1:$E$1

I also have a vector per row, say A2:E2, A3:E3, A4:E4…

Now I want output in Column F as

F2 = sumproduct(A1:E1,A2:E2)
F3 = sumproduct(A1:E1,A3:E3)
F4 = sumproduct(A1:E1,A4:E4)
...

Now I want to put these into one array formula, but I don't know how to do it. For example

F2 = Arrayformula(sumproduct($A$1:$E$1,A2:A:E2:E))

Meaning multiply A1-E1 vector with A2:E2, then A3:E3 and so on.

How do I do this?

Best Answer

=ARRAYFORMULA(IF(LEN(A2:A&B2:B&C2:C&D2:D&E2:E), 
 MMULT(A1:E1*A2:E,TRANSPOSE(COLUMN(A1:E1)^0)), ))

Related Solutions

Google-sheets – How to COUNT rows with ARRAYFORMULA

In addition to previous posts, I think I would go for a query function. Although not that short , it will eliminate the need 1) to drag the current formula down (newly added rows will 'automatically be added to the output of the query) and 2) to have the unique formula to populate col A on the count sheet.

=query(Incidents!B2:C; "select B, count(C) where B<>''  group by B label B 'Name', count(C) 'Number of incidents' ")

EDIT: Closer to the topic, if a list of names is already given (in col A), a count in arrayformula should be possible by doing:

=ArrayFormula(countif(split(concatenate(if(len(Incidents!B2:B),Incidents!B2:B&char(9),)),char(9),0), filter(A:A, len(A:A))))

Google-sheets – Compute the total of every row with arrayformula

The main goal is to make sum ArrayFormula. The original brilliant solution was provided here.

And your formula is:

=ArrayFormula(
IF($Q2:Q="No",
  SUMIF(IF(COLUMN(J2:P2),ROW(A2:A)),ROW(A2:A),J2:P),
IF($Q2:Q="Yes",
  SUMIF(IF(COLUMN(R2:X2),ROW(A2:A)),ROW(A2:A),R2:X),
IF($Q2:Q="",""))))

Best Answer

Related Solutions

Google-sheets – How to COUNT rows with ARRAYFORMULA

Google-sheets – Compute the total of every row with arrayformula

Related Topic