How to get the SUMPRODUCT of unequal arrays Google Spreadsheet

1
votes

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
google-sheetsformula

1 Answers

1
votes

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!