Information From 2 Coluumns

[AFIORE]

I am unsure if this was answered already but I am having a lot of trouble. I am trying to find out if it is possible to count the number of entries in one column if it meets criteria in another comlumn?

Here is my problem: I work for a contractor and I am trying to find out the number of contracts sold(column J) by Mike (Column K). I have tried =COUNTIFS functions but these dont work. I know if I create 4 other columns with IF functions I can get the information but I have to imagine there is another way. Please Help!

 

[AFIORE]

Maybe I was a little deceiving with my knowledge of excel. Amy could you explain what you mean by filtering and then using a subtotal

 

[AmyC]

So do you need to know how many blanks there are aswell? What version of excel are you using?

in 2007:

Select your column headers (The first row of the spreadsheet, which includes the lables for the column - "Salesperson" "Amount sold" etc.)
On the home tab, the right hand side, the drop down menu under sort and filter, choose "filter".

On the right hand side of each of the label cells, there will be a little downward arrow in a grayish box. Click the grayish box, and then unselect "select all", and then select "mike". Only rows which had "mike" in the salesperson column will now show. Go ahead and unfilter it before you work with the formulas!

For ease of looks, I actually prefer to have the totals area above the data, but it is up to you. Leaving one row or column of blank space between the cell you choose, and your data enter a variation of the following formula into the cell you wish to show the total number of contracts mike got: =Subtotal(2, range) 2 is the function number, in this case count (counts the cells with numerical values in them). The range should be which ever cells you want to subtotal - you should be able to just select them on the sheet as you are typing in the formula.

Now play with filtering the "salesperson" column again. The subtotal cell should change depending upon how many entries there are for each person you select.
Again, not the best for reports, but really easy for quick referance!

let me know if you have any aditional questions... If you have trouble, PM me for my email address, and you can send me a breif example of the spreadsheet, and I'll help you set it up!