Solved Column summation calculations (1 Viewer)

[tihmir]

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

 

[June7]

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"), "<>" & ""))

 

[tihmir]

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

 

[June7]

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.