Counting instances based on two criterias

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.

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

"[email protected]" escreveu:

> 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.
>
>

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.

"[email protected]" wrote:

> 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.
>
>

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.
<[email protected]> wrote in message
news:[email protected]...
> 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.
>

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.


Miguel Zapico wrote:
> 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.
>
> "[email protected]" wrote:
>
> > 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.
> >
> >

"[email protected]" wrote:
> =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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

--
Kevin Vaughn


"[email protected]" wrote:

> 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.
>
>
> Miguel Zapico wrote:
> > 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.
> >
> > "[email protected]" wrote:
> >
> > > 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.
> > >
> > >
>
>

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

Less typing (and Tastes great, too!)

Heather Heritage wrote:
>
> 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.
> <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >

--

Dave Peterson