0

I have the following Excel formula that sums all values from a specific column (Hrs):

=SUMPRODUCT(G3:ZZ7*(MOD(COLUMN(G3:ZZ7),3)=1))

Here is the spreadsheet enter image description here

How do I make the formula apply to specific rows, e.g. from 2 to 4?

  • 1
    I don't completely understand why you have the MOD and COLUMN formulas, which just yield 1. But in any case, can you not just change the range in both cases to G2:ZZ4? – Josh Friedlander Apr 20 '17 at 14:08
  • @JoshFriedlander The reason MOD and COLUMN are there is because, in case of Hrs, I want to be able to calculate sum of all values of every third column starting with the first Hrs. To answer the second question, the ranges cannot be changes, i.e. they should be changed dynamically. For example, user is supposed to be allowed to provide the rows range and that should be a parameter to the formula, thus "filtering" the G3:ZZ7 to, say, rows from 2 to 4 only. – Boris Apr 20 '17 at 16:18
  • Add two more arrays to your SUMPRODUCT. eg. ...(ROW(your_table)>=2)*(ROW(your_table)<=4) – Ron Rosenfeld Apr 20 '17 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.