Using a COUNTIF statement with an AND operator
in [Functions]
|
From: JTurner on 27 May 2010 16:37 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??!!
From: JTurner on 27 May 2010 16:42 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
From: JTurner on 27 May 2010 18:02 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??!! > >
From: Tom-S on 29 May 2010 11:03 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 >
From: Ashish Mathur on 30 May 2010 23:29 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" <JTurner(a)discussions.microsoft.com> wrote in message news:7D0E9DCF-2FF2-4994-ACE0-A2F488105706(a)microsoft.com... > 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 >
|
Pages: 1 Prev: Calculation for amt. Exceeding $106,800 for SS Income Limit Next: Find and Replace |