Solved Column summation calculations (1 Viewer)

tihmir

Registered User.
Local time
Yesterday, 21:07
Joined
May 1, 2018
Messages
216
I need help to calculate a few columns, but I don't know how to do it. So:

I have a workbook with 8 sheets - day1, day2, day3, day4, day5, day6, day7 and totalDay.
In each sheet I have same table with 6 columns - column1, column2, column3, column4, column5, column6
Cell in each column is List with only one text. At the end of each column I have funcion to "=COUNTA" to count all filled cells.

Then the sum for each column and each day I save in totalDay with function =+day1!E47+day2!E47+day3!E47+day4!E47+day5!E47+day6!E47+day6!E47,
BUT in the sheet "totalDay" the columns from column1 to column5 I made rows - row1, row2, row3, row4, row5, only column6 I kept it as column.
In each sheet for each row an entry can be made in one of the columns form column1 to column5 and sometimes there may be a record in column6.
What I meen:
entry in column 2 and entry in column6,
entry in column 5 and entry in column6,
entry in column 3 and entry in column6,
entry in column 1 and entry in column6,
entry in column 5 and entry in column6,

So, in totalDay, where the columns form column1 to column5 are already rows (row1, row2, row3, row4, row5)
and only column6 is I kept it as column I need to cout:
row1 -> how meny entry there are in column6 for all days
row2 -> how meny entry there are in column6 for all days
row3 -> how meny entry there are in column6 for all days
 

Attachments

  • Report.zip
    20.8 KB · Views: 360

June7

AWF VIP
Local time
Yesterday, 20:07
Joined
Mar 9, 2014
Messages
4,374
What is this data for?

Why do days 3 and 7 have 6 and 4 rows instead of 5?

totalDay allows for 5 row totals yet day 3 has 6 rows and day 7 has only 4.

Showing Column6 by row totals next to the pivoted column totals doesn't make sense.

This seems to return desired value:

=NOT(ISBLANK('day1'!F6)) + NOT(ISBLANK('day2'!F6)) + NOT(ISBLANK('day3'!F6)) + NOT(ISBLANK('day4'!F6)) + NOT(ISBLANK('day5'!F6)) + NOT(ISBLANK('day6'!F6)) + NOT(ISBLANK('day7'!F6))

An alternative is to enter sheet names in cells on sheet totalDay and assign range name TabList. Then formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'" & TabList & "'!F6"), "<>" & ""))
 
Last edited:

tihmir

Registered User.
Local time
Yesterday, 21:07
Joined
May 1, 2018
Messages
216
What is this data for?

Why do days 3 and 7 have 6 and 4 rows instead of 5?

totalDay allows for 5 row totals yet day 3 has 6 rows and day 7 has only 4.

Showing Column6 by row totals next to the pivoted column totals doesn't make sense.

This seems to return desired value:

=NOT(ISBLANK('day1'!F6)) + NOT(ISBLANK('day2'!F6)) + NOT(ISBLANK('day3'!F6)) + NOT(ISBLANK('day4'!F6)) + NOT(ISBLANK('day5'!F6)) + NOT(ISBLANK('day6'!F6)) + NOT(ISBLANK('day7'!F6))

An alternative is to enter sheet names in cells on sheet totalDay and assign range name TabList. Then formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'" & TabList & "'!F6"), "<>" & ""))
These are data for performed inspections per person in different objects.

Тhe purpose of column 6 (Joint Inspection) is to show in what kind of object the inspection was joint with other people

Еach day there may be a different number of inspections, ie. different number of rows in the table
I changed the table. I am sending the new version
 

Attachments

  • Report_v2.zip
    22.7 KB · Views: 366

June7

AWF VIP
Local time
Yesterday, 20:07
Joined
Mar 9, 2014
Messages
4,374
My suggested formulas still apply. Change sheet name and row number references as appropriate. You should try.

I would probably build Access database instead of Excel.
 

Users who are viewing this thread

Top Bottom