Help on Excel (Count Values)

[itsalllowercase]

Hey Guys,

I'm working on an Excel project and been pretty unsuccessful. I hope someone can help me.

I have a table with the following values:

[TABLE="width: 500, align: center"]
<TBODY>[TR]
[TD="align: center"]Site[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]Tenure[/TD]
[TD="align: center"]Department[/TD]
[/TR]
[TR]
[TD="align: center"]Site 1[/TD]
[TD="align: center"]ABC, Joe[/TD]
[TD="align: center"]Trainee[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Dep 1[/TD]
[/TR]
[TR]
[TD="align: center"]Site 1[/TD]
[TD="align: center"]DEF, Jane[/TD]
[TD="align: center"]Regular[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Dep 2[/TD]
[/TR]
[TR]
[TD="align: center"]Site 2[/TD]
[TD="align: center"]GHI, Joe[/TD]
[TD="align: center"]Regular[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Dep 3[/TD]
[/TR]
[TR]
[TD="align: center"]Site 3[/TD]
[TD="align: center"]JKL, Jim[/TD]
[TD="align: center"]Trainee[/TD]
[TD][/TD]
[TD="align: center"]Dep 7[/TD]
[/TR]
</TBODY>[/TABLE]


I need it to:
1) Count all cells with Site 1, Site 2 and Site 3. (This I was able to do with COUNTIF).
2) Count all trainees from Site 1.
3) Count all trainees from Site 1 under Department 1.

Is there an easy way to do this?

Thanks in advance!

 

[Pascal]

Hi,

You need to use SUMPRODUCT: -

=SUMPRODUCT((A2:A5="Site 1")*(C2:C5="Trainee"))
=SUMPRODUCT((A2:A5="Site 1")*(C2:C5="Trainee")*(E2:E5="Dep 1"))

 

[itsalllowercase]

It worked when I had 2 arrays but gave an NA when I added the thrid array.

 

[Pascal]

So the last example I gave didn't work, or did you type it in yourself?

Which range is your data in, and on the one that gave you N/A is there data in the Cells to match?

Regards

 

[itsalllowercase]

This is the exact formula I've used:

=SUMPRODUCT((A2:A685="Site 1")*(F2:F685="Trainee")*(H2:H480="Dep 1"))

 

[Pascal]

This is the exact formula I've used:

=SUMPRODUCT((A2:A685="Site 1")*(F2:F685="Trainee")*(H2:H480="Dep 1"))

Change the H480 to H685.

Regards