Using a COUNTIF statement with an AND operator

[JTurner]

I'm using Excel 2003 so I hope I'm not limited here.

We are performing a space usage survey and I need to compile the data
results. Each location has 70 entries over the course of 10 days. I have a
column for "group work" where the value is either 1 or 0. I have another
column for # of people observed in a particular office. What I'm trying to do
is the following:

Get a count of instances where "# of people" = 1 and "group work" = 0

I'm doing a lot of countif statements, but I don't see how to do a countif
with an and operator. Help??!!

 

[JTurner]

Anyone? Please??!! Here is another example from the same survey...

I need to count how many total there are where ".ppl" = 2 and use_mtg = 1. I
can easily get this via pivot table or filters, but I need it in a text
report using formula. There are sixty million of these types of formulas I
have to make...and they all are basically the same thing. If I could get ONE
to work, I can do the rest.

date time building room .ppl use_mtg
05/03/10 8:30 AM 3 3013 0
05/03/10 10:00 AM 3 3013 1 0
05/03/10 11:30 AM 3 3013 3 1
05/03/10 1:00 PM 3 3013 0
05/03/10 2:30 PM 3 3013 2 1
05/03/10 4:00 PM 3 3013 2 0
05/03/10 5:00 PM 3 3013 2 0
05/04/10 8:30 AM 3 3013 3 1
05/04/10 10:00 AM 3 3013 3 1
05/04/10 11:30 AM 3 3013 2 1
05/04/10 1:00 PM 3 3013 0 0
05/04/10 2:30 PM 3 3013 2 1
05/04/10 4:00 PM 3 3013 0 0
05/04/10 5:00 PM 3 3013 2 1
05/05/10 8:30 AM 3 3013 3 0
05/05/10 10:00 AM 3 3013 1 0
05/05/10 11:30 AM 3 3013 2 0
05/05/10 1:00 PM 3 3013 0 0
05/05/10 2:30 PM 3 3013 0 0
05/05/10 4:00 PM 3 3013 0 0
05/05/10 5:00 PM 3 3013 0 0
05/06/10 8:30 AM 3 3013 1 0

 

[JTurner]

I plugged in your formula, adjusted the parameters and I get a total of 0. If
I were to filter the data, I'd get 13 records that meet this criteria.

 

[Tom-S]

The formula suggested by Ronaldo is the same as I would have given, so if
it's not producing the correct result there's a few possible reasons.

The first to check is whether your data is formatted as text. The formula
suggested is looking for numeric data, but if your data is entered as text
then you need to include double quote marks e.g.

=SUMPRODUCT((A1:A1000="1")*(B1:B1000="0"))

Same would apply to your data in the .ppl and use_mtg columns.

If that doesn't work let me know.

Regards,

Tom

 

[Ashish Mathur]

Hi,

Ronaldo's formula should get you the desired result. One possible reason
for it not working would be that the .ppl column ad use_mtg column has text
instead of numeric entries. Select the .ppl column, go to Data > Text to
columns and click on Finish. Repeat the same for the use_mtg column. Now
the formula should work

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com