?
Solved

Google Spreadsheet SUMPRODUCT with dynamic row count.

Posted on 2015-02-14
2
Medium Priority
?
181 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are many add-ons for Google docs, slides and sheets that can be extremely helpful in keeping your files organized and simple to use. Add-ons are installed into your Google docs, slides or sheets through Google's Add-On Store. One of my favorit…
First of all let me say that the only language that I speak is English, but in answering questions here I often come across people whose English skills are not the best and I’d like to be able to communicate better with them, and the following descr…
This Micro Tutorial will introduce a Google tool, which is a great way to learn more about dimensions in metrics in Google Analytics, even if you use the interface or the API.
This Micro Tutorial demonstrates in Google Analytics how to create a custom report that shows you traffic over time using the month of year dimensions. There are also instructions on how to fix Google's odd month of year formatting, which Microsoft …

765 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