Countif & AND functions

[Mick]

Finally gave up and have to ask the question..
I have a simple spreadsheet with 5 columns, column B is places, column D is
a statement of "Done" or "NOT Done".

All I want to do is count the number of records there are per place that are
NOT Done.

eg column B includes a place called Central London, I want to count the
number of records if B= "Central London" and D= "NOT Done".

Any assistance would be gratefully received.
Many thanks

 

[Lars-Åke Aspelin]

Finally gave up and have to ask the question..
I have a simple spreadsheet with 5 columns, column B is places, column D is
a statement of "Done" or "NOT Done".

All I want to do is count the number of records there are per place that are
NOT Done.

eg column B includes a place called Central London, I want to count the
number of records if B= "Central London" and D= "NOT Done".

Any assistance would be gratefully received.
Many thanks

Try this formula:

=SUMPRODUCT((B1:B100="Central London")*(D1100="NOT Done"))

Change the 100 to suit the size iof your data in column B and D

Hope this helps / Lars-Åke

 

[Shane Devenshire]

Hi,

If you enter the location you want to count in F1 then your formula would be

=SUMPRODUCT(--(B1:B100=F1),--(D1100="Not Done"))

or in 2007:

=COUNTIFS(B:B,F1,D,"Not Done")

 

[Mick]

Many thanks to you both I thought I had tried everything.
Both versions worked.

Many thanks
Mick