Countif

The UK's most regular instructor-led training courses.
Training information: microsoft+excel+training · Microsoft+excel+training · Microsoft Excel Training UK
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Low Priority · Version 2003

7 posts

ReplyMon 1 Mar 2010, 16:00Delegate Angelo said...

Angelo has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Access Advanced course
Access VBA course

Countif

How do you count if for more than one if? Count if row1 = 1 AND row2<30?

Version	Date	Location	Places available	Book	Next place rate (£)		List price
Excel Advanced 1 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice	List price
2007	Mon 2 Aug 2010	Southwark	3		£240	£245	£245
2003	Thu 5 Aug 2010	Southwark	4		£240	£245	£245
2010	Fri 6 Aug 2010	Bloomsbury	0	FULL			£299
2007	Tue 10 Aug 2010	Bloomsbury	0	FULL			£245
2003	Tue 10 Aug 2010	Limehouse	5		£235	£240	£245
2007	Fri 13 Aug 2010	Limehouse	8		£229	£234	£245
Full Schedule: See all 134 Excel Advanced course dates. Bookings currently available until 28th September 2011.

452 posts

ReplyMon 1 Mar 2010, 16:54Trainer Anthony said...

RE: countif

Hi Angelo, thanks for your query. The trick is to add up the two separate COUNTIF results. In this formula, the final result is the Sum of two COUNTIF formulas:

=(COUNTIF(A1:A16, ">15"))+(COUNTIF(B1:B16, ">15"))

Alternatively,

=Sum((COUNTIF(A1:A16, ">15")), (COUNTIF(B1:B16, ">15")))

Hope this helps,

Anthony

7 posts

ReplyTue 2 Mar 2010, 08:32Delegate Angelo said...

RE: countif

Suppose I have:
=(COUNTIF(A1:A16, ">15"))+(COUNTIF(B1:B16, ">15"))

How can this be generalised to count regardless of what number I choose for 15 without going into the code? In other words, How do I do the following:

=(COUNTIF(A1:A16, >C1))+(COUNTIF(B1:B16, >C2))

where C1 is any number I choose?

7 posts

ReplyWed 3 Mar 2010, 16:20Delegate Angelo said...

RE: countif

I think the solution you provided equates to counting if it satisfied one condition OR another condition. I need it to only count if it satisfies BOTH conditions.

This is what I have so far. I've relabeled the terms to what I'm actually doing:

Names Box:
Database=A1:B30
Disease=A1:A30
Age=B1:B30

Cells:
C1=0
C2=30
D1=COUNTIF(Disease,"="&C1)

but I need something that will take into account their age, something like:

=COUNTIF(Database,Disease=C1&Age<C2)

But that doesn't work...

452 posts

ReplyMon 8 Mar 2010, 14:23Trainer Anthony said...

RE: countif

Hi Angelo, sorry for the delay in replying. You need to put in, say, cell E1 the text >15 (without any quotes) and then reference cell E1 in your COUNTIF statements instead of putting ">15" as your criteria. Try that out and see how you get on.

Anthony

7 posts

ReplyMon 8 Mar 2010, 21:15Delegate Angelo said...

RE: countif

This is what I have so far. I've relabeled the terms to what I'm actually doing:

Names Box:
Database=A1:B30
Disease=A1:A30
Age=B1:B30

Cells:
C1="0"
C2="30"

So I have a column of peoples age (A1:A30) and a column of 1s and 0s for whether or not they have a disease.
I need to count how many people are under the age of 30 AND have the disease. Also, I need the function to refer to other cells that I can easily change the criteria from "under 30" to "under" any number I want. Or to change having the disease (1) to not having the disease (0). So the final function should look something like this:

D1=COUNTIF(Database,Disease=C1&Age<C2)

but this does not work. Any suggestions?

452 posts

ReplyTue 16 Mar 2010, 18:06Trainer Anthony said...

RE: countif

Hi Angelo, soryr for the delay in replaying - very busy training at the moment. With regards to your final function I'm a bit worried about the following:

Disease=C1&Age<C2

You appear to be trying to concatenate two separate functions here. Try using AND(Disease=C1, Age<C2) instead of the ampersand and put that into the COUNTIF statement. Let me know how you get on and whether you need any further assistance.

Anthony

Tue 23 Mar 2010: Automatically marked as resolved.

· Share And Share Alike - Use Excel To Collaborate As A Winning Team!
· Tips From Excel's 'Rarely Used' File
· What Makes A Good Spreadsheet?
· Inviting Investment Into Your Business Can Be Easier With Excel
· How To Embed Fonts In Excel

Please browse our web site to find out more about
microsoft+excel+training and other Microsoft training courses.

Excel tip:

Reconciling a list to correspond with another

May have a list that needs to make but on another sheet one list seems to be out, eg. product list one sheet contains all product and inventory data, while the other contains pricing data. Both need to match with all products but there is more products in one list than the other. To find the disparaging product compare data in the two columns that need to match Make sure that order the same way.
Create another column in the sheet that has the most items and type in the first cell

=Exact(text1,text2) text1 being the cell that you want compared with text2 cell reference. Drag to filldown and your first false will give you for first cells that does not match. Correct insert the row with data in other sheet and continue the process until all the data returns true. Delete the column inserted.

View all Excel hints and tips