sumproduct

[flashbbeb]

Hi all,

I'm a fan of SUMPRODUCT, but seem to run into issues at every turn. I'm trying to get a count of rows based on two criteria - the name of a course (column A), and the department (column M) that are on another sheet (qdata) in the same file.

I am trying to use the formula on a summary sheet (Q108summary):

=SUMPRODUCT(--(qdata!$A$1:qdata!$A$30000=$A2),--(qdata!$M$1:qdata!$M$30000=$D$1))

...where "qdata" is another sheet, A1 through A30000 is the column with raw data on courses, and A2 refers to a course name on the summary sheet in the same row where I'm using the formula. The M1 through M30000 is the column on the "qdata" sheet with department names. The $D$1 is a reference in the summary sheet to a particular department.

Summary sheet:
            human resources     accounting
course 1    FORMULA GOES HERE
course 2    FORMULA GOES HERE

qdata sheet:
A         ...M
course1      human resources
course2      human resources
course3      accounting

This seems like a very basic use of the formula, but I keep getting #N/A.

Any thoughts? I've got to be missing something obvious here!

Thanks,
EB

 

[SkipVought]

Hi,

This is a question that should be posted in forum68.

course1 <> course 1
etc.

Therefore, NADA!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]