sumproduct?

pimail · Jul 12, 2011

Hi
i have a table C3:AZ331
this represents a school timetable with columns for 50 periods.
in each column there are units of 3 cells with information, e.g.
C3 13A/BS1 (representing a subject)
C4 BS (representing a subject code)
C5 ABC (representing the initials of a teacher)

the columns extend to show the subjects for each year group 13 through 7 taught on each period of the timetable.

I want to be able to count the number of times teacher "abc" is assigned to class 13A/BS1 and the same for all staff and all subjects for each year group.

The end result i would like is a table with teacher’s initials in one column, all the subjects as a header row and the number of times each teacher is teaching a given subject then populates the table.

I have tried variations on sumproduct but end up with a 0 result each time.

Can anyone help or offer other solutions please?

Thank you

Steve=True · Jul 13, 2011

I would suggest posting your sample file so that a direct solution can be created. It is possible, but just confusing with so many fields.

post your file on www.4shared.com

Joe4 · Jul 13, 2011

We would recommend posting a screen shot using one of the tools mentioned here before resorting to uploading files a file storage site. There are three main reasons for this:

1. It is easier to see (right in the post)
2. Some of us work for places that do not allow us to download files from the internet (so you have a better chance getting replies if you can contain everything necessary right in the thread itself)
3. Many of those storage sites have expirations of the files may be taken down at a later time, which can render the thread pretty useless, especially when our goal is to create a searchable database.

pimail · Jul 13, 2011

pimail · Jul 13, 2011

above is a small extract of the table.

any subject e.g. 13C/Ar1 can appear in any column, but will not appear again in the same column.

any subject may appear in more than one 'row' (a row in this case being a set of 3 rows of linked information e.g. rows 3,4,5 are linked data as shown in the table.

also a teacher 'TSH' may be associated with other classes, e.g. 13B/Ar2, 8-2/AR etc and other teachers may be attached to each class.

the outcome i am looking for is a table with column headings of all possible classes, the left column listing all possible staff and the table then showing how many times each teacher is assigned to each class.

i hope this clarifies things but if not please let me know

thank you

john

Joe4 · Jul 13, 2011

I am afraid I am not going to be able to be much help here. I have a feeling that the way the data is constructed could make this difficult to do. It is quite messy.

If it were me, I would create this as a relational database and do it in Microsoft Access, which means that the data would have to be entered into multiple tables in a normalized manner, which would enable the data to be worked with much more easily.

sumproduct?

pimail

New Member

Excel Facts

Steve=True

Well-known Member

Joe4

MrExcel MVP, Junior Admin

pimail

New Member

pimail

New Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

sumproduct?

pimail

New Member

Excel Facts

Steve=True

Well-known Member

Joe4

MrExcel MVP, Junior Admin

pimail

New Member

pimail

New Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock