Automatically count weeks?

Hi there

Hereīs the deal: based on weekly reports, I need to count and sum orders created in our CRM.

Iīm exporting, once a week, a full report of opportunities created in our CRM. Week after week I copy&paste the changes to my main file or dashboard. This allows me to see, manually four values my sales manager wants:

1. Opportunities created in the last week
2. Opportunities created in the last 2 weeks
3. Opportunities created in the last month
4. YTD

I donīt need help with the last one, thatīs the easy part. The thing is, I have to do this manually. Every thursday I run the report, export it, find the new opportunities and add them to my main report. Then, I just modify an already defined IF formula that counts and a SUMIF one that sums the values, so it will take into account only the last week, the one before that one and the whole month.

So, my questions are...

1. I need to set different formulas for count and sum, thatīs clear, but how can I make this autimatically without having to change the formula each week?
2. Do I need to consider the date my main file is modified, and count backwards?


As usual I am not sure Iīm being clear, though I hope Iīm getting better at this. Iīm attaching an example where B2:D7 is similar to my main report, and G4:M12 is my DataTable.

The formulas in C4, C5, C6, D4, D5, D6 should be "automatic", so when I copy&paste the extra rows from the weekly reports into my datatable, those cells will count and sum without me having to change the period in the formulas.

Hope I am clear enough for you guys to help me out, as usual.

Thanks!!

Alejandro

c4=SUMPRODUCT(--($M$5:$M$12>=TODAY()-7),$L$5:$L$12)
d4=SUMPRODUCT(--($M$5:$M$12>=TODAY()-7))
no. of days = 7
hope this is what you are looking for.
best wishes
sreedhar

Here is one option (see encl. file)

=SUMPRODUCT(((TODAY()-$G$3:$G$10)<--(LEFT($J6,2)))*($F$3:$F$10))

HTH
Ola

Hi there!

Thanks for the replies and answers! This formulas work just fine, though I have one question: every time I add more lines or rows to the data table, I will need to modify the formulas, right? i.e.: I add 10 more lines next week, so now the range wonīt be G3:G10, but G3:G20, and the same for the F column, so that needs to be changed every week.

Is there a way to avoid this? I mean, just drop the new lines there and the formula calculate it without having to modify the ranges?

Thanks!

Alejandro

Is there a way to avoid this? I mean, just drop the new lines there and the formula calculate it without having to modify the ranges?

Hi Alejandro,
this is entirely possible using named ranges.
You can find a very nice explanation here

Cheers

Arthurbr,
Thanks for the reference. Iīm experimenting with dynamic ranges and see what happens. I already created the dynamic range, though Iīm not sure what to do next. What should I do or add to the formulas?

Iīm attaching the example.

Thanks!

Alejandro

Here is an example that should work (see encl. file)

F_range: =OFFSET(Sheet1!$F$3,0,0,COUNT(Sheet1!$F:$F))
G_range: =OFFSET(Sheet1!$G$3,0,0,COUNT(Sheet1!$G:$G))

Remember, not to put a new (second) list under the first list. The formula counts All numbers under the F - G column.
I would removed your previous range, since it use COUNTA which counts text as well. COUNT() only counts numbers. If you like to rename the ranges, it's no problem.

HTH
Ola

Hi there!

Thanks, I see the difference. I tried COUNT but I got an error before. In this example you sent thouh, I donīt see the error. Iīll check it.

Again, thanks. This really helps!

Alejandro

Happy it worked
Tacks for the feedback
Ola

Hi Itīs me again.

This is working wonders for me, thank you guys for helping me.

Hereīs another question. Please consider all thatīs been done till now and the example you helped me with.

There are basically 2 formulas in the "stats" sheet.

One:
=SUMPRODUCT(--ISNUMBER(MATCH(DataTable!$E$3:$E$9,SourceCodes!$D$5:$D$10,0)),--(DataTable!$G$3:$G$9="FY08"),DataTable!$F$3:$F$9)

Two:
=SUMPRODUCT(--ISNUMBER(MATCH(DataTable!$E$3:$E$9,SourceCodes!$D$5:$D$10,0)),--(DataTable!$G$3:$G$9="FY08"))

One of them counts, the other sums the data in the "DataTable" sheet. Now, Iīve been using the "Created" column to split things created in a certain moment (FY08 or FY09), thatīs what you can see in the formulas above.

But what I need this value to be a range instead of a simple value? Iīve grouped in the "SourceCodes" sheet all FYs in 3 different groups (In my actual sheet there are quite more than 3), and added a new table in the "stats" sheet.

What I need to do now is the same we did in the previous example, but instead of filtering through one simple value (i.e. "FY08" or "FY09" as in the first table in "stats"), I need to match this condition to each FY Period you will find in the SourceCodes sheet.

So, how do I replace the "FY08" in these formulas with a specific range?

Thanks guys

Ale