Help in choosing formula

[BurkeDad]

Need some help in what ype of formula I need to use to obtain the answer I need.

In column A I have a list of all the facilities in our organization. In column C I have the type of employee (fulltime, volunteer, contractor, etc....) in the facility. In a cell, I need to create a formula that returns the number of fulltime workers in each facility. I wrote a sumproduct formula - which reads - =sumproduct(--(A$3$:A$1678$=n$5$, C$3$:C$1678$=n$2$)). N5 is the facility number pull from a drop down list and n2 is the type of employee (designated by a number (1, 2, 3, 4, etc....). When I execute the formula, I get #value as the error.

What am I doing wrong - or should I be using a different formula all together?

 

[VoG]

Try

=SUMPRODUCT(--(A$3:A$1678=N$5), --(C$3:C$1678=N$2))

 

[T. Valko]

Need some help in what ype of formula I need to use to obtain the answer I need.

In column A I have a list of all the facilities in our organization. In column C I have the type of employee (fulltime, volunteer, contractor, etc....) in the facility. In a cell, I need to create a formula that returns the number of fulltime workers in each facility. I wrote a sumproduct formula - which reads - =sumproduct(--(A$3$:A$1678$=n$5$, C$3$:C$1678$=n$2$)). N5 is the facility number pull from a drop down list and n2 is the type of employee (designated by a number (1, 2, 3, 4, etc....). When I execute the formula, I get #value as the error.

What am I doing wrong - or should I be using a different formula all together?

Try it like this...

=SUMPRODUCT(--($A$3:$A$1678=$N$5),--($C$3:$C$1678=$N$2))

If you're using Excel 2007 or later...

=COUNTIFS($A$3:$A$1678,$N$5,$C$3:$C$1678,$N$2)

 

[BurkeDad]

Okay, put the -- and () in and it now returns a value of "0". Now, N5 is a formula (=left(n4,4) that takes the value of the pulldown selection and breaks out a certain part of the facility number. And when I change the "left" formula into an hard keyed value, the formula wrrks. What gives?

 

[VoG]

If N5 is meant to be a number try

=SUMPRODUCT(--(A$3:A$1678=N$5+0), --(C$3:C$1678=N$2))

 

[BurkeDad]

Okay. I tried the countifs and It worked. Still curious about the sumproduct value issue.

 

[BurkeDad]

Thanks. Just saw the rely about the value. I'll try thanks.