How do I count values across multiple columns?

E

elsenorjose

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

Bob Phillips

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


--

HTH

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

brucek

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

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))
Click to expand...

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
 
Top