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.
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
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.
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
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.
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
