QuestionBank

Google sheets How to get the SUMPRODUCT of unequal arrays Google Spreadsheet

Google sheets How to get the SUMPRODUCT of unequal arrays Google Spreadsheet,google-sheets,formula,Google Sheets,Formula,Google states that SUMPRODUCT "calculates the sum of the products of corresponding entries in two equal-sized arrays or ranges". What I want to do is get the sum of the products of corresponding entries in two unequal-sized arrays. Such a thing can be accomplished with Excel: =SUMPRODUCT((WEEKDAY(A1:Z1,2)>5)*(A2:Z10="self")) This will display a sum of all the times when "self" occurs in rows 2 through 10 where row 1 is a weekend. In Google Spreadsheets, the equivalent should be =ArrayF

Google states that SUMPRODUCT "calculates the sum of the products of corresponding entries in two equal-sized arrays or ranges".

What I want to do is get the sum of the products of corresponding entries in two unequal-sized arrays. Such a thing can be accomplished with Excel:

``````=SUMPRODUCT((WEEKDAY(A1:Z1,2)>5)*(A2:Z10="self"))
``````

This will display a sum of all the times when "self" occurs in rows 2 through 10 where row 1 is a weekend. In Google Spreadsheets, the equivalent should be

``````=ArrayFormula( SUMPRODUCT(WEEKDAY(A1:Z1,2)> 5, A2:Z10="self" ) )
``````

However because Google Spreadsheets can only accept equal-sized arrays, this will fail. So as I see it I have three options:

1. Insert 8 more rows between row 1 and 2 with a copy of all the dates from row 1
2. Redo the formula with 8 sums, ex.:

=ArrayFormula( SUMPRODUCT(...)) + ArrayFormula( SUMPRODUCT(...)) ...etc...

1. Find another formula that will work more efficiently

So the question boils down to point 3 - what other formulas could I use to make an array sum of rows 2 through 10 so that I can multiply them inside of SUMPRODUCT? Or is there another function that would help me accomplish the same thing?

Thanks!

#1

I don't know how I missed it, but the code for Excel works perfectly in Google Spreadsheets as well. So here's the final answer:

``````=ArrayFormula(SUMPRODUCT( ( weekday(A1:Z1,2)>5 ) * ( A2:Z10 = "self" ) ))
``````

Hopefully someone else finds this useful!

#2

Thankyou for taking the time to post your own answer, yes I found this useful, cheers!