COUNTIF FUNCTION
 

How can I COUNTIF values in Column A = "Manchester" AND values in column B =
"Overdue"
i.e I have a sheet with lots of locations in Column A, and Overdue or "OK"
in Column B, but i want to know how many overdues there arevfor particular
locations!

Hi Lisa

one way

=SUMPRODUCT(--(A1:A1000="Manchester"),--(B1:B1000="Overdue"))

note however that you can not use A:A or B:B with SUMPRODUCT

for more details on how this function works check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Cheers
julieD


"Lisa" wrote in message
...
How can I COUNTIF values in Column A = "Manchester" AND values in column B
=
"Overdue"
i.e I have a sheet with lots of locations in Column A, and Overdue or "OK"
in Column B, but i want to know how many overdues there arevfor particular
locations!

Hi,
To count the numbers only,try this
=countif(A1:A1000,"Manchester")+countif(B1:B1000," Overdue")

Thanking you,
K.S.Warrier

"JulieD" wrote:

Hi Lisa

one way

=SUMPRODUCT(--(A1:A1000="Manchester"),--(B1:B1000="Overdue"))

note however that you can not use A:A or B:B with SUMPRODUCT

for more details on how this function works check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Cheers
julieD


"Lisa" wrote in message
...
How can I COUNTIF values in Column A = "Manchester" AND values in column B
=
"Overdue"
i.e I have a sheet with lots of locations in Column A, and Overdue or "OK"
in Column B, but i want to know how many overdues there arevfor particular
locations!

Hi KS

this will give a count of ALL Manchester plus ALL overdue - not overdue in
Manchester.
i.e.
Location Status
Manchester Paid
Machester Overdue
Lincolnshire Overdue

you're formula will return 4 not 1

Cheers
JulieD

"K.S.Warrier" wrote in message
...
Hi,
To count the numbers only,try this
=countif(A1:A1000,"Manchester")+countif(B1:B1000," Overdue")

Thanking you,
K.S.Warrier

"JulieD" wrote:

Hi Lisa

one way

=SUMPRODUCT(--(A1:A1000="Manchester"),--(B1:B1000="Overdue"))

note however that you can not use A:A or B:B with SUMPRODUCT

for more details on how this function works check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Cheers
julieD


"Lisa" wrote in message
...
How can I COUNTIF values in Column A = "Manchester" AND values in
column B
=
"Overdue"
i.e I have a sheet with lots of locations in Column A, and Overdue or
"OK"
in Column B, but i want to know how many overdues there arevfor
particular
locations!

Another way to summarize the data is by using a pivot table. There are
instructions in Excel's help, and some instructions and links he

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Create a pivot table with Location in the row area, and Status in the
column area. Add another copy of Location to the Data area, where it
will become Count of Location.

Lisa wrote:
How can I COUNTIF values in Column A = "Manchester" AND values in column B =
"Overdue"
i.e I have a sheet with lots of locations in Column A, and Overdue or "OK"
in Column B, but i want to know how many overdues there arevfor particular
locations!


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Brilliant! - Thanks Julie

"JulieD" wrote:

Hi Lisa

one way

=SUMPRODUCT(--(A1:A1000="Manchester"),--(B1:B1000="Overdue"))

note however that you can not use A:A or B:B with SUMPRODUCT

for more details on how this function works check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Cheers
julieD


"Lisa" wrote in message
...
How can I COUNTIF values in Column A = "Manchester" AND values in column B
=
"Overdue"
i.e I have a sheet with lots of locations in Column A, and Overdue or "OK"
in Column B, but i want to know how many overdues there arevfor particular
locations!

Brilliant - Thanks Julie!Lisa

"JulieD" wrote:

Hi Lisa

one way

=SUMPRODUCT(--(A1:A1000="Manchester"),--(B1:B1000="Overdue"))

note however that you can not use A:A or B:B with SUMPRODUCT

for more details on how this function works check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Cheers
julieD


"Lisa" wrote in message
...
How can I COUNTIF values in Column A = "Manchester" AND values in column B
=
"Overdue"
i.e I have a sheet with lots of locations in Column A, and Overdue or "OK"
in Column B, but i want to know how many overdues there arevfor particular
locations!

you're welcome

"Lisa B" <Lisa wrote in message
...
Brilliant - Thanks Julie!Lisa

"JulieD" wrote:

Hi Lisa

one way

=SUMPRODUCT(--(A1:A1000="Manchester"),--(B1:B1000="Overdue"))

note however that you can not use A:A or B:B with SUMPRODUCT

for more details on how this function works check out
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Cheers
julieD


"Lisa" wrote in message
...
How can I COUNTIF values in Column A = "Manchester" AND values in
column B
=
"Overdue"
i.e I have a sheet with lots of locations in Column A, and Overdue or
"OK"
in Column B, but i want to know how many overdues there arevfor
particular
locations!