January 2021 Tip Of The Month

January 2021 News & Tips

NEWS		SAGE 50		EXCEL
Happy New Year!		The new 1099-NEC form		Counting items in Excel

News

Happy New Year! I hope you're doing well in the new year. If you're reading this then you've made it through the unbelievable year that was 2020. As strange as 2020 was, it was not as big a change for me as it was for most people because I've been working from home ever since starting IQ Accounting Solutions in 2008. And let's face it, accountants and computer nerds aren't known for being the most outgoing people anyway.

I want to thank all of you for choosing IQ Accounting Solutions. Every new year brings its own challenges and rewards, and I look forward to serving each of you as we work to make the most of 2021.

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

A complete archive of these tips is available at http://www.iqaccountingsolutions.com/blog

Sage 50/Peachtree Tip - Printing the New 1099-NEC

You've probably heard by now that non-employee compensation now must be reported on form 1099-NEC, instead of the 1099-MISC. The 1099-MISC still exists for other types of payments (rents, royalties, attorney fees, etc.) but non-employee compensation, (payments to contractors) now have their own form.

How does this affect Sage 50 users? It depends on how you print 1099s.

For payroll subscribers you can print the 1099-NEC on blank perforated forms using Aatrix. Simply go to Reports & Forms > Forms > Tax Forms. Then double click Payroll Tax Forms in the list of forms. When the Payroll Tax Form Selector window opens, choose 2020 1099-NEC from the list of federal forms, select the 2020 for the year, click OK and follow the prompts in the wizard. You will need to be on release 2021.1.1 and have the January 2021 update installed in order for the 1099 data to automatically populate in Aatrix. If, for some reason you can't update to the latest release you can still use Aatrix but you will have to manually enter the 1099 information. You may find it helpful to review the knowledgbase article 2020 Aatrix 1099 Tech Tips before printing 1099s through Aatrix.

For those who aren't payroll subscribers, or anyone who wants to print on pre-printed forms, you'll need to download the latest 1099 formats from Sage. Sage has provided two options. One is formatted to fit the forms you can order from the IRS, the other is designed for the 2-per-page forms you get from Sage or other forms suppliers. To download the new 1099-NEC and 1099-MISC as well as 1096 forms, follow the link to one of these two knowledgebase articles, depending on which format you want to use.

1099-NEC Filing Forms for use with Sage Checks & Forms(L1096/L99A Laser)
1099-NEC Filing Forms for use with IRS scannable fileable forms

At the bottom of both articles are links to download the related forms. You will need to save the files to your Forms folder. If you don't know where your forms folder is located, the easiest way to find it is to go to the Maintain menu from the main Sage 50 window, then choose Company Information. In that window you'll see a greyed out field labeled Directory. This shows you where the current company is located, and from that you can figure out where your forms folder is. If you are a single user and you installed to the default location, the Directory will be C:\Sage\Peachtree\Company\xxxxxxxx, where "xxxxxxxx" is the name of the folder your company is stored in. But the important part for this conversation is everything before that. In this scenario, the forms folder would be located at C:\Sage\Peachtree\Company\Forms.

Once you have saved the files to the Forms folder you can go to the Reports & Forms menu > Forms > Tax Forms. You will now see the new 1099 formats in the list of forms and you can print them as in previous years.

Regardless of which method you use to print 1099s, one important thing to keep in mind is that Sage has not added a way to distinguish between 1099-NEC vendors and 1099-MISC vendors. So if you need to print both types you will need to manually delete (Aatrix) or de-select (Sage internal forms) vendors from the list during the 1099 printing process.

Excel Tip - Counting Entries in Excel

Usually we think of adding numbers on a spreadsheet, but sometimes you just need to count the items in a list. If you have tried to use Excel's COUNT() function you may or may not have gotten the results you wanted.

There are two very similar functions in Excel: COUNT() and COUNTA(). The difference between them is that COUNT only counts cells containing numbers but COUNTA counts all cells that aren't empty. Think of it as "Count Anything". Speaking of empty cells, there is also a COUNTBLANK() function that will count only empty cells.I know I said "two ways to count", so think if this as a bonus.

The syntax is the same for all three and is very simple. The only thing you need in the parentheses is the range of cells you want to count. You can also include multiple ranges by separating them with commas. For example: =COUNT(A1:A5,G10:G13) would look at cells A1 through A5 as well as cells G10 through G13.

So to show how each function works, here you can see formulas for COUNT, COUNTA, and COUNTBLANK.

	COLUMN A	COLUMN B	COLUMN C
ROW 1	1	a	1
ROW 2	2	b	2
ROW 3	3	c	3
ROW 4			a
ROW 5			b
ROW 6			c
ROW 7	=COUNT(A1:A6)	=COUNT(B1:B6)	=COUNT(C1:C6)
ROW 8	=COUNTA(A1:A6)	=COUNTA(B1:B6)	=COUNTA(C1:C6)
ROW 9	=COUNTBLANK(A2:A6)	=COUNTBLANK(B2:B6)	=COUNTBLANK(C2:C6)

And here you can see the result of each formula:

	COLUMN A	COLUMN B	COLUMN C
ROW 1	1	a	1
ROW 2	2	b	2
ROW 3	3	c	3
ROW 4			a
ROW 5			b
ROW 6			c
ROW 7	3	0	3	<--COUNT
ROW 8	3	3	6	<-- COUNTA
ROW 9	3	3	0	<-- COUNTBLANK

Now you know how to count how many numbers are in a list, how many of anything are in a list, or even how many of anything aren't in a list. If you still can't get the count that you need, try reading my previous tip about using COUNTIF to count items that meet specific conditions.

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