I have a set range but now I would like to narrow that down by using two
criteria. I used one criteria before but now I have two. Any advice is
greatly appreciated. Thanks.

=sumproduct((a2:a22="criteriaone")*(b2:b22=1))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AAS" wrote in message
...
I have a set range but now I would like to narrow that down by using two
criteria. I used one criteria before but now I have two. Any advice is
greatly appreciated. Thanks.

You can accomplish multiple criteria results using Array functions. Take a
look at Chip Pearson's web page at the following URL and scroll down until
you find the section on Logical Functions with arrays:

http://www.cpearson.com/excel/ArrayFormulas.aspx

Hope this points you in the proper direction...
--
Kevin Backmann


"AAS" wrote:

I have a set range but now I would like to narrow that down by using two
criteria. I used one criteria before but now I have two. Any advice is
greatly appreciated. Thanks.

still not to sure what im doing wrong here. i've got two worksheets, i'm
using this formula on worksheet 2. i have two columns on worksheet one,
column 1's criteria is called "Memco", column 2's criteria is "PFH". I would
like to take all the memco in column 1 that also have PFH in column 2 and
count them.


worksheet 1
worksheet 2

Don Jones Memco PFH
quantity- 2
Jerry Davids
Tom Tanner Memco
John Doe Memco PFH

The answer would be two to this example






"Don Guillett" wrote:

=sumproduct((a2:a22="criteriaone")*(b2:b22=1))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AAS" wrote in message
...
I have a set range but now I would like to narrow that down by using two
criteria. I used one criteria before but now I have two. Any advice is
greatly appreciated. Thanks.

worksheet 1

Don Jones Memco PFH
Jerry Davids
Tom Tanner Memco
John Doe Memco PFH


worksheet 2

quantity 2



the answer would be two to this example


"AAS" wrote:

still not to sure what im doing wrong here. i've got two worksheets, i'm
using this formula on worksheet 2. i have two columns on worksheet one,
column 1's criteria is called "Memco", column 2's criteria is "PFH". I would
like to take all the memco in column 1 that also have PFH in column 2 and
count them.


worksheet 1
worksheet 2

Don Jones Memco PFH
quantity- 2
Jerry Davids
Tom Tanner Memco
John Doe Memco PFH

The answer would be two to this example






"Don Guillett" wrote:

=sumproduct((a2:a22="criteriaone")*(b2:b22=1))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"AAS" wrote in message
...
I have a set range but now I would like to narrow that down by using two
criteria. I used one criteria before but now I have two. Any advice is
greatly appreciated. Thanks.

in your previous formula, replace rng=value by (rng1=value1)*(rng2=value2).

Other than that give us some example data and your formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"AAS" wrote in message
...
I have a set range but now I would like to narrow that down by using two
criteria. I used one criteria before but now I have two. Any advice is
greatly appreciated. Thanks.

worksheet 1

John Doe Memco PFH
Jerry Jones PFH
Tom Tanner Memco
David Toms Memco PFH
Jeff Smith Memco

worksheet 2

quantity 2

I am trying to count how many employees are Memco and PFH both. I have been
trying to use the countif formula but cannot figure out how to use two
criteria. The formula is being used on worksheet 2.









"Bob Phillips" wrote:

in your previous formula, replace rng=value by (rng1=value1)*(rng2=value2).

Other than that give us some example data and your formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"AAS" wrote in message
...
I have a set range but now I would like to narrow that down by using two
criteria. I used one criteria before but now I have two. Any advice is
greatly appreciated. Thanks.

=SUMPRODUCT(--(B2:B20="Memco"),--(C2:C20="PFH"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"AAS" wrote in message
...
worksheet 1

John Doe Memco PFH
Jerry Jones PFH
Tom Tanner Memco
David Toms Memco PFH
Jeff Smith Memco

worksheet 2

quantity 2

I am trying to count how many employees are Memco and PFH both. I have
been
trying to use the countif formula but cannot figure out how to use two
criteria. The formula is being used on worksheet 2.









"Bob Phillips" wrote:

in your previous formula, replace rng=value by
(rng1=value1)*(rng2=value2).

Other than that give us some example data and your formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"AAS" wrote in message
...
I have a set range but now I would like to narrow that down by using two
criteria. I used one criteria before but now I have two. Any advice is
greatly appreciated. Thanks.

=SUMPRODUCT('Production Calendar'--(C11:C97="Memco"),--(D11:D97="PFH"))

this is my exact formula that will not work(it comes up as an error). i am
taking columns C and D from worksheet 1 and if two of the columns are the
same then i want it to count them.
thanks alot









"Bob Phillips" wrote:

=SUMPRODUCT(--(B2:B20="Memco"),--(C2:C20="PFH"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"AAS" wrote in message
...
worksheet 1

John Doe Memco PFH
Jerry Jones PFH
Tom Tanner Memco
David Toms Memco PFH
Jeff Smith Memco

worksheet 2

quantity 2

I am trying to count how many employees are Memco and PFH both. I have
been
trying to use the countif formula but cannot figure out how to use two
criteria. The formula is being used on worksheet 2.









"Bob Phillips" wrote:

in your previous formula, replace rng=value by
(rng1=value1)*(rng2=value2).

Other than that give us some example data and your formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"AAS" wrote in message
...
I have a set range but now I would like to narrow that down by using two
criteria. I used one criteria before but now I have two. Any advice is
greatly appreciated. Thanks.

If, and that is a big IF as you are not telling, worksheet 1 is 'Production
Calendar' then

=SUMPRODUCT(--('Production Calendar'!C11:C97="Memco"),--('Production
Calendar'!D11:D97="PFH"))


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"AAS" wrote in message
...
=SUMPRODUCT('Production Calendar'--(C11:C97="Memco"),--(D11:D97="PFH"))

this is my exact formula that will not work(it comes up as an error). i am
taking columns C and D from worksheet 1 and if two of the columns are the
same then i want it to count them.
thanks alot









"Bob Phillips" wrote:

=SUMPRODUCT(--(B2:B20="Memco"),--(C2:C20="PFH"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"AAS" wrote in message
...
worksheet 1

John Doe Memco PFH
Jerry Jones PFH
Tom Tanner Memco
David Toms Memco PFH
Jeff Smith Memco

worksheet 2

quantity 2

I am trying to count how many employees are Memco and PFH both. I have
been
trying to use the countif formula but cannot figure out how to use two
criteria. The formula is being used on worksheet 2.









"Bob Phillips" wrote:

in your previous formula, replace rng=value by
(rng1=value1)*(rng2=value2).

Other than that give us some example data and your formula.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"AAS" wrote in message
...
I have a set range but now I would like to narrow that down by using
two
criteria. I used one criteria before but now I have two. Any advice
is
greatly appreciated. Thanks.