Solved

Google Spreadsheet SUMPRODUCT with dynamic row count.

Posted on 2015-02-14
2
169 Views
Last Modified: 2015-02-16
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
Comment
Question by:cinco-pata5
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 40611076
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
 

Author Closing Comment

by:cinco-pata5
ID: 40612487
Perfect it worked great.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can provide a virtual interface for remote stakeholders in a SWOT analysis through a Google Drawing template. By making real time viewing and collaboration possible, your team can build a stronger product.
Explore the encryption capabilities built into Google Apps and how these features can help you meet privacy policy and regulatory compliance, but are not a full solution. Understand and compare the most popular email encryption services for Google A…
This Micro Tutorial will demonstrate without sampling how to find out top organic landing pages. The hack gets around the standard way to find the pages in Google Analytics results in sampling for larger sites.
This Micro Tutorial will demonstrate how to analyze your website's back links using a network graph powered by a fusion table.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question