Log In
Register
Home
September 27, 2018
Offline
Hi
I am a student, I have an Excel file called "Attendance" I use it to record attendance data and absence of lectures at the university.
The Excel file contains 15 sheets, each sheet containing attendance data and absence for one week
Each sheet used to record the attendance or absence data (1 or 0) of each lecture, in addition to the sheet "statistics" which I use it to put the totals of attendance and absence.
What is the proper function that I should put in the "Statistics" sheet in which I can know the number of absences for each lecture?
I attached the Excel file
Best Regards,
AbuElia
VIP
Trusted Members
June 25, 2016
Offline
Hi AbuElia
As long as your worksheets formats are consistent, you can use 3-D referencing.
To create a formula with a 3-D reference in the argument, perform the following steps:
- Click the cell where you want to enter your 3D formula.
- Type the equal sign (=), enter the function's name, and type an opening parenthesis, e.g. =SUM(
- Click the tab of the first worksheet that you want to include in a 3D reference.
- While holding the Shift key, click the tab of the last worksheet to be included in your 3D reference.
- Select the cell or range of cells that you want to calculate.
- Press the Enter key to complete your Excel 3-D formula.
Hope this helps
Sunny
September 27, 2018
Offline
Hi Sunny
Thank you for your kind reply
I want to use SUMIF
because my goal is to count number of 0's cells
I want something like: =SUMIF('1:15'!B2;"=0";'1:15'!B2)
Thanks
VIP
Trusted Members
June 25, 2016
Offline
Since you only want to count cells with a 0 (excluding blank cells and cells with text) then in cell B2 of the Statistics sheet, enter
=COUNT('1:15'!B2)-SUM('1:15'!B2)
Copy the formula to the other cells.
Explanation:
COUNT('1:15'!B2) will count the number of numeric cells only (0's & 1's excluding blanks and text)
SUM('1:15'!B2) will sum numeric cells only. This will give the cells containing 1's
The difference between the two is the number of 0's
Hope this helps.
Sunny
1 Guest(s)
