Adding Monthly Refresh of Data

[msanders]

Hello Everyone,

I've searched the forum but have not been able to find a solution that I can adapt. Here is my problem:

I have a set of data (company, date contacted, PI Total, and PO Total) that is entered periodically in the spreadsheet. On a monthly basis, I'd like the PI totals and # of companies in the database to be summed automatically and have a new row created to store the data. Does anyone on this forum know how I might do this?

Thanks,
Matt

[zbor]

Try this in B15 =SUMPRODUCT(--($B$3:$B$8<=$A15))
and in C15 =SUMPRODUCT(--($B$3:$B$8<=$A15);$C$3:$C$8)

(replace ; with , in second formula if needed)

[contaminated]

Didn't entirely understand but try this in B15

Please Login or Register  to view this content.

And in C15

Please Login or Register  to view this content.

Copy 'em down

[martindwilson]

not sure if you need sumproduct at all!
=COUNTIF($B$3:$B$10,"<="&A15)
and
=SUMIF(B$3:B$10,"<="&A15,C$3:C$10)

[msanders]

Thanks for the help everyone. It is counting the companies and adding the numbers like I want, but I may not have been clear enough about the monthly update.

I'd like for the data that has been entered in the 'Database' section to be collected every month (for example on the first of every month) automatically and summarized in the Database tracking section in a new row of data in the Database Tracking section.

Do any of these functions accomplish that?

Attached is the latest .xls with your suggestions implemented. Please let me know if you have any questions.

Thanks,
Matt

[Palmetto]

I would use a Pivot Table based on a dynamic named range.

See attached - sheet2.

A dynamic named range was created for the database and used as the source for the pivot table.

Dynamic Named Ranges automatically expand/contract as data are added and removed.

You only need to right-click the pivot table and choose "Refresh" after making changes in the database.

[msanders]

Thanks alot!

[Palmetto]

If you are satified with your answers please mark the thread as solved and be sure to rate the answers.