October 2018 News & Tips

 

NEWS		SAGE 50		EXCEL
Sage Drive finally goes multi-user!		Vendor Management Center puts vendor activity at your fingertips		SUMIFS lets you sum items selectively based on multiple conditions

News

Multi-user Sage Drive: Sage Drive is a Sage 50c feature that allows you to share your Sage 50 company data with users outside your office. When the feature was first introduced, sharing your data meant that only one user could access that company, no matter how many user licenses you had. Later they improved things a little by giving all other users read-only access while the company is shared. Both times I freely expressed my opinion that to be useful, this feature had to allow full access to users in the office even while the data is shared on Sage Drive. Recently Sage announced that this has finally happened.

If you're interested in this feature, I recommend reading Sage's blog post Sage Drive Multi-User now available for Sage 50cloud - U.S. Edition!. Be sure to watch the video at the bottom of the post too. Here are some key points:

• Multi-user functionality is an additional subscription that can be purchased by contacting the Sage 50 sales team.
• All users will need a fast internet connection, including the upload speeds. In different places Sage recommends either 20 or 25 mbps download speeds and 10mbps upload speeds for best performance, and a required minimum of 10 mbps download and 5 mbps upload speeds (or 3 depending on which article you read) for small companies.
• You will need to be on at least version 2019.0 and all users must be on the same version.

I have not had an opportunity to test this new functionality but I'm glad to see Sage has come through with this feature that they have been promising. I haven't seen pricing for the additional subscription yet. But the fact that there is an additional subscription combined with the warning in the video that performance will be slower while the company is shared leads me to think that anyone who wants to share their Sage 50 data with more than a few users would be better off setting up a Terminal Server. Or if you don't want to maintain your own Terminal Server, you can check into hosting services for Sage 50. Sorry but I don't have any recommendations for hosting companies. Of course both of those options have significant costs of their own, but I would expect either of them to provide better speed and reliability.


Steve Collins
IQ Accounting Solutions LLC
918-851-9713
scollins@iqaccountingsolutions.com
www.iqaccountingsolutions.com  

---

 

Sage 50/Peachtree Tip - Vendor Management Center

If you're looking for the quickest way to see a vendor's activity, including accounts payable invoices, payments, purchase orders and more, you need the Vendor Management Center. You'll find it by clicking on Vendors & Purchases on the left side of the main Sage 50 window. Two tabs will be near the top of the window. The second tab is the Vendor Management Center.

Select a vendor ID to see transactions. Double clicking on a transaction will open it. By default the Vendor Management Center has three sections: Purchase Orders, Payments, and Item Purchase History. But clicking the Customize button at the top of the screen lets you choose from:

• Aged Payables
• Expense Tickets
• Find a Report
• Item Purchase History
• Payments
• Purchase Orders
• Purchases/Inventory Received
• Time Tickets
• Top Vendors: Last Twelve Months
• Total
• Vendor Credit Memos
• Vendor Information
• Vendors & Purchases Tasks
• Write Checks

In the Customize window you can also use the Move Up and Move Down buttons to change the order of the select modules, or use the Rename button to change the description on each module, or click the Restore Defaults button to return to the original settings.

Most of the modules let you choose a date range and have a customize button so you can choose which columns you want to see. Clicking on a column heading will sort the transaction based on the column. Click it again to reverse the sort order. You can also click the Advanced Search link to search within any of the displayed columns.

Most of the information in the Vendor Management Center can be found other places. But nowhere else puts all of this information in one place.



  Forward To A Friend
 

---

 

Excel Tip - Add Up Numbers When They Meet Multiple Conditions using SUMIFS().

Have you ever wanted to add up only certain numbers within a list? Maybe it was sales over a certain dollar amount. Or you you have a list of payments and you want to know the total of all payments with a payment type of "Check". If you only have one criteria for selecting the numbers to add, you can use Excel's SUMIF function. But if you have multiple criteria, you need SUMIFS.

The format for the SUMIFS function is fairly simple: =SUMIFS(Sum_Range,Criteria_Range1,Criteria1,Criteria_Range2,Criteria2).

Sum_Range is the range of cells that the numbers to be summed will come from.
Criteria_Range1 is the range cells to look in for the first condition that must be meet in order for a number in the sum range to be included in the sum.
Criteria1 is the criteria that Excel will look for in Criteria_Range1.
You can have up to 127 criteria (although I hope for your sake that you never need that many)

Even though the format is simple I prefer using the Insert Function wizard. The wizard's fill-in-the-blank interface makes it easier to keep track of which argument you are entering and it shows you the results as you go. And it automatically surrounds criteria text in quotation marks for you.

Let's use this list of payments for a couple of examples.
 

	A	B	C	D
1	Date	Payment Method	Payee	Amount
2	3/7/19	Check	Gwinnett County Water	73.42
3	3/9/19	EFT	Hubbard Wholesale	1,500.00
4	3/12/19	Card	Clooney Chemical Supply	124.68
5	3/18/19	Check	Poole, Gene	200.00
6	3/25/19	EFT	Mills Leasing Corp.	550.00
7	3/30/19	Card	Daniel Lawn Pro, Inc.	335.50
8	4/1/19	Check	Gene Poole	450.00
9	4/13/19	EFT	Akerson Distribution	1,000.00
10	4/14/19	Card	Mr. Gene Poole	147.00
11
12				4,380.60
13
14			EFT	2,500.00
15			Mr Poole	650.00

Cell D14 is summing all payments greater than or equal to $1,000 with a payment method of EFT. Here are two variations on the formula.

=SUMIFS(D2:D10,D2:D10,">=1000",B2:B10,"EFT")

This is saying: sum any number from cell D2 to D10 if the number in cell D2  to D10 is greater than 1000, AND if the cell on the corresponding row in column B contains "EFT".

Here's a variation that gets the same result using a slightly different method.
=SUMIFS(D2:D10,D2:D10,">1000",B2:B10,C14)

Here, instead of specifically telling Excel to look for "EFT" in cells B2 through B10, we tell it to look for whatever is in cell C14. Since C14 is not surrounded with quotation marks, Excel interprets it as a cell reference instead of as text to be matched.

In cell D15 we totaled all checks to Mr. Poole. But we know that our data entry people aren't always consistent with how they enter names,so we wanted to sum any cell in D2 through D10 when column B exactly equals "Check" and column C contains "Poole" anywhere within the cell contents. That formula looks like this:
=SUMIFS(D2:D10,B2:B10,"Check",C2:C10,"*Poole*")

You can see that there is an * before and after Poole. That means there can by any text before and/or after Poole. "*Poole" would look for text ending with Poole and "Poole*" would look for text beginning with Poole.



  Forward To A Friend
 

---

If you know other Sage 50/Peachtree users, do them (and me) a favor and pass this along to them. If you are someone that this has been passed on to, and would like to be added to my email list, you can subscribe for free at www.iqaccountingsolutions.com/signup.html or send me an email at newsletter@iqaccountingsolutions.com. If you need to change your email address or would like to unsubscribe, you can use the links at the end of this email.

Steve Collins
IQ Accounting Solutions LLC
918-851-9713
10611 E 17th Place
Tulsa, OK 74128