How do I count values across multiple columns?

[elsenorjose@sbcglobal.net]

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.

[Bob Phillips]

=SUMPRODUCT(--(D2:D8611>0),--(E2:E8611>0))


--

HTH

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


<elsenorjose@sbcglobal.net> wrote in message
news:1136846564.545421.66110@g47g2000cwa.googlegroups.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.
>

[elsenorjose@sbcglobal.net]

Thanks

[brucek]

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:D8611>0),--(E2:E8611>0))
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> <elsenorjose@sbcglobal.net> wrote in message
> news:1136846564.545421.66110@g47g2000cwa.googlegroups.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.
> >
>
>
>

[Biff]

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

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:D8611>5),--(E2:E8611<10))

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

Using the above example the result woud be 1.

Biff

"brucek" <brucek@discussions.microsoft.com> wrote in message
news:167BA7AA-5E08-4409-9F56-59F3664995D1@microsoft.com...
> 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:D8611>0),--(E2:E8611>0))
>>
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> <elsenorjose@sbcglobal.net> wrote in message
>> news:1136846564.545421.66110@g47g2000cwa.googlegroups.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.
>> >
>>
>>
>>