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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|