I have the following fields in a spreadsheet:

Order Number
Product A
Product B

I created the following to count the number of orders that had 1 or
more of Product A or 1 or more of Product B in them:

Count of Prod A: (=COUNTIF(D2:D8611, "0")
Count of Prod B: (=COUNTIF(E2:E8611, "0")

Now, what I'd like to do is create a column that will display the
number of orders that have both Product A and Product B.

I've tried this: =(COUNTIF(D2:D8611,"0")+COUNTIF(E2:E8611,"0")) but
that gives me the sum of both fields where there is 1 or more product.
Thanks in advance.

=SUMPRODUCT(--(D2:D86110),--(E2:E86110))


--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
I have the following fields in a spreadsheet:

Order Number
Product A
Product B

I created the following to count the number of orders that had 1 or
more of Product A or 1 or more of Product B in them:

Count of Prod A: (=COUNTIF(D2:D8611, "0")
Count of Prod B: (=COUNTIF(E2:E8611, "0")

Now, what I'd like to do is create a column that will display the
number of orders that have both Product A and Product B.

I've tried this: =(COUNTIF(D2:D8611,"0")+COUNTIF(E2:E8611,"0")) but
that gives me the sum of both fields where there is 1 or more product.
Thanks in advance.

Thanks :)

Using this sumproduct formula, what would be the syntax to find a value say
greater than 5 but less than 10?

"Bob Phillips" wrote:

=SUMPRODUCT(--(D2:D86110),--(E2:E86110))


--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
I have the following fields in a spreadsheet:

Order Number
Product A
Product B

I created the following to count the number of orders that had 1 or
more of Product A or 1 or more of Product B in them:

Count of Prod A: (=COUNTIF(D2:D8611, "0")
Count of Prod B: (=COUNTIF(E2:E8611, "0")

Now, what I'd like to do is create a column that will display the
number of orders that have both Product A and Product B.

I've tried this: =(COUNTIF(D2:D8611,"0")+COUNTIF(E2:E8611,"0")) but
that gives me the sum of both fields where there is 1 or more product.
Thanks in advance.

Using this sumproduct formula, what would be the syntax to find a value say
greater than 5 but less than 10?
=SUMPRODUCT(--(D2:D86110),--(E2:E86110))

What does "find a value" mean?

That formula COUNTS the number of times that column D is greater than 0
while the corresponding cell in column E is also greater than 0.

To COUNT the number of instances where column D is 5 AND column E is <10:

=SUMPRODUCT(--(D2:D86115),--(E2:E8611<10))

D...............E
5................9
6................10
7................9

Using the above example the result woud be 1.

Biff

"brucek" wrote in message
...
Using this sumproduct formula, what would be the syntax to find a value
say
greater than 5 but less than 10?

"Bob Phillips" wrote:

=SUMPRODUCT(--(D2:D86110),--(E2:E86110))


--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
ups.com...
I have the following fields in a spreadsheet:

Order Number
Product A
Product B

I created the following to count the number of orders that had 1 or
more of Product A or 1 or more of Product B in them:

Count of Prod A: (=COUNTIF(D2:D8611, "0")
Count of Prod B: (=COUNTIF(E2:E8611, "0")

Now, what I'd like to do is create a column that will display the
number of orders that have both Product A and Product B.

I've tried this: =(COUNTIF(D2:D8611,"0")+COUNTIF(E2:E8611,"0")) but
that gives me the sum of both fields where there is 1 or more product.
Thanks in advance.