Time-table

[Chris m=ec]

I'm trying again. I posted this problem previously under "Two different
criterias in one formula?" So I'm going try to explain it again.

Sheet 1 is a huge time-table/subject roster. Sheet 2 is a reference for me
to double-check if the correct subject had been assigned to the appropriate
trade group.

1. In sheet 1 col C (labelled "Group") is the different trade groups in
each row, col D (labelled "Subjects") the appropriate subject in the
appropriate row for each group in col C, col E (labelled "Venue"), col F
(labelled "Lecturer"). This whole range repeats from col G onwards and again
from col K and so on, and it carries on for 2nd years, 3rd years and 4th
years. If the problem can be solved for the 1st years, it's just a matter of
copying the formula to the next intake group.

2. Sheet 2 Cell B1:K1 are the different trade groups and Cell A2:A30 the
different subjects. The first problem is that I don't work with numerical
values in both sheets but with text. Sheet 2 must be marked with an "x" in
each corresponding cell if the appropriate subject had been assigned to the
appropriate group. Example:
B1 are labelled "L1BM"
A2 is "Engineering Science".
If "Engineering Science" is assigned to "L1BM" somewhere in sheet 1, then an
"x" must show up in sheet 2 cell B2.

Basically Excel must lookup "L1BM" in sheet 1, see if "Engineering Science"
is assigned to the adjacent cell and return with "x" in sheet 2.
Unfortunately SUMIF only works with numerical during summing, or am I wrong?

Thanks for giving this problem attention.

 

[bpeltzer]

The bigger issue with sumif is that it deals with a single criterion, whereas
you're looking for a match of both subject and group. DSUM and SUMPRODUCT
are better suited to a multi-criteria match. If I understand correctly, you
need the 'x' to appear as long as there's at least one instance of the group
and subject appearing in the same row.
I think this should do; enter in Sheet2, cell B2 and copy across and down
=if(sumproduct(--(Sheet1!$C$1:$C$1000=B$1),--(Sheet1!$D$1:$D$1000=$A2))>=1,"x",0)

 

[Chris m=ec]

That does it for C1:C1000!!

Thanks, now it's just a matter of time to get this formula to work over the
whole range (C1:N1000).