Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » CountIf (counting two arguments)

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

CountIf (counting two arguments)

Resolved · High Priority · Version 2003

Reply Mon 8 Jun 2009, 10:25Delegate Gareth said...

Gareth has attended:
Excel VBA Intro Intermediate course
Excel Intermediate course
Excel Advanced course

CountIf (counting two arguments)

Hi All

I have a database where I need to count two arguments.

I have two columns that I need to count. One the 'District' where the data is either labelled D1, D2 or D3.

Secondly I have a Priority Column labelled either "Gold" "Silver" or "Bronze"

First of all I need to look in column K to see what District is entered, then I need to go over to column U and count how many Districts labelled "D1" have the priority status "Gold"

I need something to return D1 has 5 Gold Status Priorites etc etc

I have tried using the countif function but become stuck.

Any help greatly appreciated.

Regards

Gareth.

For upcoming training course dates see: Pricing & availability

Reply Mon 8 Jun 2009, 11:36Trainer Amanda said...

RE: CountIf (counting two arguments)

Hello Gareth

Thank you for your question.

There are a couple of ways you could go about doing this.

Firstly you could filter the 'District' column, then filter the 'Priority' column; Excel should give you a count on the left hand side of the status bar at the bottom of your screen to tell you how many records are showing in the filter results (displayed as X of Y records).

Secondly you could filter the 'District' column, then use the Subtotal function to count only visible cells in the Priority column. Because your Priority column in text-based, use the Subtotal for Counta rather than Count. More explanation is provided here:
http://www.ozgrid.com/Excel/excel-subtotal-function.htm

Thirdly you could use an advanced filter, where you set up your criteria separately from your list/database. For more information look up advanced filter in Excel help or see:
http://www.contextures.com/xladvfilter01.html for an example.

I hope this helps.

Kind regards
Amanda

Reply Mon 8 Jun 2009, 11:47Delegate Gareth said...

RE: CountIf (counting two arguments)

Amanda

Thanks for the response. However, is there out of curiosity a function or formula that could achieve this?

On my database im trying to have a front page that consolidates and summarises the data for other people.

Thanks again

Gareth.

Reply Mon 8 Jun 2009, 11:54Trainer Amanda said...

RE: CountIf (counting two arguments)

Hi Gareth

Thanks for the further explanation.

I think that SUMPRODUCT might help you, I'm not really familiar with this function myself but there is a bit of information about it here:
http://www.contextures.com/xlFunctions01.html#SumProduct

Let me know how you get on with using this, if you have any problems send me a reply and I'll have a look into it.

Kind regards
Amanda

Reply Mon 8 Jun 2009, 12:43Delegate Gareth said...

RE: CountIf (counting two arguments)

Amanda

A million thanks yous, the sum product function work wonders!

I also have another query for this particular database that I could run past you if you dont mind?!

In one of the columns I have worked out how many days there are between two dates.

At the front of my database I want a formula that works out how many clients will be returning in the next 7 days.

So basically a function that counts the number of entries >= 0 and <= 7 in a column.

Any ideas?

Thanks again.

Reply Mon 8 Jun 2009, 13:09Trainer Amanda said...

RE: CountIf (counting two arguments)

Hi Gareth

Glad to hear that worked out :)

For your second question, I think you can get Excel to calculate this for you by creating a formula that subtracts the result of one COUNTIF from another, to count how many entries fall within a certain range - see here:
http://www.contextures.com/xlFunctions04.html#Range

Kind regards
Amanda

Reply Fri 31 Jul 2009, 10:02John (guest) said...

RE: CountIf (counting two arguments)

Hi Gareth
you could try this assuming that your data id in Col F the range is F1 to F6 this can be changed =SUMPRODUCT(--($F$1:$F$6>0),--($F$1:$F$6<8))

Excel articles

· Reporting and Analysis with Excel
· What's Better Than the Help Function in Excel? Microsoft Training
· How to Create Better Excel Spreadsheets: Part Four
· How To Print Worksheets In Excel
· Excel 2010 Can Make Your Reports Better And More Detailed Than Ever

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

Excel tip:

Edit and format multiple worksheets in one operation

To select two or more non-adjacent worksheets, click on the tab of each worksheet, while holding down the [CTRL] key.
To select two or more adjacent worksheets, click on the tab of the first worksheet and then on the tab of the last worksheet, while holding down the [SHIFT] key.
Enter or modify your data and apply the necessary formatting options to the sheet that's displayed

View all Excel hints and tips