COUNTIF FUNCTION

[Lisa]

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!

 

[JulieD]

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

 

[K.S.Warrier]

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

Thanking you,
K.S.Warrier

 

[JulieD]

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

 

[Debra Dalgleish]

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

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]

Brilliant! - Thanks Julie

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

Brilliant - Thanks Julie!Lisa

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

 

[JulieD]

you're welcome

Brilliant - Thanks Julie!Lisa