A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using a COUNTIF statement with an AND operator



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 09:37 PM posted to microsoft.public.excel.worksheet.functions
JTurner
external usenet poster
 
Posts: 10
Default Using a COUNTIF statement with an AND operator

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??!!

  #2  
Old May 27th, 2010, 09:42 PM posted to microsoft.public.excel.worksheet.functions
JTurner
external usenet poster
 
Posts: 10
Default Using a COUNTIF statement with an AND operator

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

  #3  
Old May 27th, 2010, 11:02 PM posted to microsoft.public.excel.worksheet.functions
JTurner
external usenet poster
 
Posts: 10
Default Using a COUNTIF statement with an AND operator

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.

"RonaldoOneNil" wrote:

Assuming number of people is in column A and group work is in column B, use
the following adjusting the range as required

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

"JTurner" wrote:

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??!!

  #4  
Old May 29th, 2010, 04:03 PM posted to microsoft.public.excel.worksheet.functions
Tom-S[_2_]
external usenet poster
 
Posts: 68
Default Using a COUNTIF statement with an AND operator

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


"JTurner" wrote:

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

  #5  
Old May 31st, 2010, 04:29 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default Using a COUNTIF statement with an AND operator

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

"JTurner" wrote in message
...
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:11 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.