Counting based on values in two separate columns

W

Wayne

Count the number of times in multiple rows that column a = sold and column b
= 1

Logically Count If A:A="sold" and b:b=1
 
T

Toppers

=SUMPRODUCT(--(A1:A1000="Sold"),--(B1:B1000=1))

Note with SUMPRODUCT you cannot use whole columns (i.e. A:A)
 
B

Biff

=SUMPRODUCT(--(A1:A100="sold"),--(B1:B100=1))

You can't use entire columns - A:A, B:B.

Better to use cells to hold the criteria:

C1 = sold
D1 = 1

=SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1))

Biff
 
R

Ron Coderre

Try this:

=SUMPRODUCT((A1:A100="sold")*(B1:B100=1))

That formula counts the instances where a Col_A value equals "sold" and the
corresponding Col_B value is 1. Note: that formula is NOT case sensitive.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Top