Counting instances based on two criterias

[macik81]

I need some help trying to count instances based on two criterias. I
have a worksheet that holds a list of records and who owns the records
and their employee number. I am trying to find a formula that will
count the humber of records a department owns and whether the record
owner is a full time employee or a contractor (based on emplyoee
number). I have tried using a Sum(If(),If()) type of function, but I
am unable to use a wildcard in the if statement, therefore it doesn't
work. The main problem I am having is determining if the record owner
is a contractor, by the cnt* in their employee number.

Any ideas????

Thanks in advance.

 

[Guest]

Hi Macik,

on way to solve it is using sumproduct, so the formula should be:

=sumproduct(--(range="variable_one"),--(range="variable_two"))

note that the "variables" could be a fixed cell..

hope this helps
regards from Brazil
Marcelo

"(e-mail address removed)" escreveu:

 

[Guest]

You can use the formula SUMPRODUCT. There you can have the two criterias
stated, and apply formulas like LEFT to the statements. For example, if the
data is in ranges A1:A50 and B1:B50
=SUMPRODUCT(--(A1:A50="Dep"),--(LEFT(B1:B50,3)="cnt"))

Hope this helps,
Miguel.

 

[Heather Heritage]

sumproduct will do it for you

=SUMPRODUCT(--(NOT(ISERROR(SEARCH("searchtext",A1:A4)))),--(b1:b4=dept))



replace A1:A4 with the actual range of the cnt* , B1:B4 with the department
range, and dept with the department being searched for.

 

[macik81]

Thank You Miguel. It works perfectly. Now I am having problems with
this function:
=SUMPRODUCT(--(LEFT('Data'!B1:B500,3)="xyz"),--(IF('Data'!F2:F500>27,1,0)))
It returns #VALUE, even after I do the ctrl-shft-enter to make it an
array funciton.

Any ideas?

Thanks.

 

[Guest]

=SUMPRODUCT(--(LEFT('Data'!B1:B500,3)="xyz"),--(IF('Data'!F2:F500>27,1,0)))
It returns #VALUE ...

One guess ..

Perhaps try correcting it to ensure that the 2 ranges are identical in size:
'Data'!B1:B500 is not equal in size to 'Data'!F2:F500

---

 

[Guest]

I would say get rid of the if,then construct
=SUMPRODUCT(--(LEFT('Data'!B1:B500,3)="xyz"),--('Data'!F2:F500>27))

Not tested, I just deleted what seemed to me to be extraneous. Also, you
won't need to enter this with ctrl-shift-enter

 

[Dave Peterson]

Instead of using Not(iserror()), you can use isnumber().

Less typing (and Tastes great, too!)