• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

Google Spreadsheet SUMPRODUCT with dynamic row count.

Hi I'm using this formula to add all items from a specific category and a specific date range.  But the problem I'm facing now is that I have another sheet where I insert all my entries, and this sheet dosen't have a max row, it can grow continuously, but in the formula I Inserted the range until A1000, but there could have more lines of code, how can I go around to put a dynamic row count on the formula.

=SUMPRODUCT(Entries!$F$3:$F$1000 * (Entries!$A$3:$A$1000 >= DATE(2015;COLUMN()-1;1)) * (Entries!$A$3:$A$1000 < DATE(2015;COLUMN();1)) * (Entries!$C$3:$C$1000 = A3))

Open in new window

0
cinco-pata5
Asked:
cinco-pata5
1 Solution
 
barry houdiniCommented:
I think SUMIFS would be preferable - you can use whole column range without any major downside

=SUMIFS(Entries!$F:$F;Entries!$A:$A;">="&DATE(2015;COLUMN()-1;1);Entries!$A:$A;"<"&DATE(2015;COLUMN();1);Entries!$C:$C;A3)

regards, barry
0
 
cinco-pata5Author Commented:
Perfect it worked great.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now