SUM/Count if 2 conditions are met

Hi guys,

i have a spreadsheet where people type in their email into cell C4 on a sheet named VIEW.

What i need is cell C5 (on the VIEW sheet) to return a count of how many people have passed a course AND align into the named person in C4. (2 criteria)

i have all the raw data on a seperate sheet called "DATA"

Column J on DATA contains the line manager email address (1st criteria)

Column T on DATA contains the status (Passed, Failed, Incomplete, Not Started)

This is the formula that i have in cell C5:


{=SUM((DATA!J1:J10000, C4)*(DATA!T2:T10000="Passed"))}

But this returns the error "#VALUE!"

Can anybody point me in the direction of what ive done wrong and how to fix this?

Thanks for your help!

It worked except i had to switch the J and T columns around to get the figure i was looking for, but thank you very much!!

Is there a maximum of criteria i can have? and is it just a case of adding the extra arguments before the present ones? i.e. to find only those passed in 2007 (Year column is F)

e.g.

=SUMPRODUCT((Data!F2:F10000="2007")*(Data!T2:T10000=C4)*(Data!J2:J10000="Passed"))

Thanks again
Rich

Thanks Dom,


=SUMPRODUCT((DATA!T2:T10000="Passed")*(DATA!J2:J10000=DISPLAY!C4))
=SUMPRODUCT((DATA!T2:T10000="Not Started")*(DATA!J2:J10000=DISPLAY!C4))
=SUMPRODUCT((DATA!T2:T10000="Failed")*(DATA!J2:J10000=DISPLAY!C4))
=SUMPRODUCT((DATA!T2:T10000="Incomplete")*(DATA!J2:J10000=DISPLAY!C4))

I now have a 1x4 table that contains the raw data that is pulled from the above formula.

The Variable (Employee name) is in cell C4. It pulls a total for each of the course status' for the direct reports of whoever is named in C4.

I have no idea how to get the next part. (I thought of using a lookup but the amount of raw data changes so often the only thing that remains constant are the column headings and that the data in column T being one of the above 4 options.)

Under this table, i require a list of only those people who have "Not Started" "Failed" or "Incomplete" a course.

The List must contain direct reports Name (From Data! sheet column B) and The Course title (from Data! sheet column K).

Anybody have an idea?

Your help is always appreciated!