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:

Site	Name	Status	Tenure	Department
Site 1	ABC, Joe	Trainee		Dep 1
Site 1	DEF, Jane	Regular		Dep 2
Site 2	GHI, Joe	Regular		Dep 3
Site 3	JKL, Jim	Trainee		Dep 7

<TBODY>
</TBODY>

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