Obtaining Counts for the SS300 Report using your Payroll History Spreadsheet - Sartox

Select the year that you need to obtain information for.

Select “List of Object Codes” from the drop down list.


List all of the object codes that apply to the categories needed for the SS300 Report.


Refresh the data.

Click on the Payroll History Pivot Table tab. Then click on the Full Account with Names button at the top – this will give us the best starting point for the report.

Drag the full acct code to the top


Drag all of the data files (such the Payline Gross, Net Pay, total hours, etc) off the table – LEAVING ONLY THE NET GROSS


Your pivot table should only contain the Full Name and the Net Gross columns.


If you have a Grand Total column on the table, right Click somewhere in the table and select Table Options

Remove the check mark on the Grand totals for rows. Click OK



Click on the Object in the top part of the pivot table and drag down ON TOP OF the Net Gross heading.


This will position your object codes across the top of the table.

If the table is too wide and you want to work with only one section at a time (for instance teachers - object code 121), then filter the object code for 121 and 181 to get the supplements.


Your table would look like this…


Click on the cell to the right of the last object code heading on the table and type in the heading “Teacher Suppl?”


On the row below the Teacher Suppl, you will use a formula to determine if the person was paid from BOTH 121 and 181.

In our case, the 121 amount is in column B and the 181 amount is in column C

The line that we are entering the formula on is D34 (row 34 in this case is the first person in the list). This formula indicates that if B34 is not equal to 0 AND C34 is not equal to 0, then put “Yes” in the cell – otherwise put “No”.

You should have either a “Yes” or “No” in the cell.  Click on the cell with the new formula (D34 in our case).   Click on the lower right corner of the cell with the formula and drag it down the entire table. This will copy the formula to the other lines.

=IF(AND(B34<>0,C34<>0),"Yes","No")



 

To count the number of people paid from 121 and the number of people with supplements, we will need 2 additional formulas.

Click on cell F30 and type “# of Supplements”. Click on cell H30 and type “# of People”


 

Click on Cell F31 and we will add a temporary formula to do a calculation for us

=COUNTIF(D:D,"Yes")

=COUNTIF(E:E,"Yes")

This formula will count any entries in Column E that have “Yes” in them. This will count the number of 121 teachers that received supplements.

Click on Cell H31 and add a temporary formula to do a calculation on for the total number of people paid from 121.

=COUNTIF(B34:B65000,">0")

This formula will count any entries in column B beginning with row 34 that are greater than 0 – thus counting the number of people paid out of 121.

Note – if you need to now break that out by Fund – filter on Fund.  For example, we will filter on Fund 2 in the top section of the table to obtain counts for Local Funds (Note – we are still looking at Object code 121).  Note that the numbers will adjust


To obtain totals for a different section, adjust the Filter for the object code. For example, to see Principals, Filter the Object Code for 114 and 181.


Note that again, the totals will adjust.  Be sure to check your filter for FUND and select All.  If you need to break down these numbers by type of School (High, Middle, Elementary) then filter by the Location filter at the top and select all locations that are Elementary etc.  Note – if you have Office 2003, you will need to drag the location to the bottom portion of the table in order to filter for multiple locations.