Excel forumula question.

[TimboBee]

First up I would like to say Hi, first time here and I signed up, I am hoping this is a great place to help sharpen my skills.

I came across Excel later in life...and LOVE IT!!! I am self taught to this point and realize there's alot to it.

My question, is this...I am trying to count cells in a colum that meet a definate criteria. But that must also meet criteria in another column...For instance column A and B have two lists...one column describes the source of the sales lead...in different categories...(Advertisment, public relations, word of mouth etc.. The second column has a rating for each lead..(A, B, C, D) I would like to count how many Advertisment leads in column A have a rating of C.
How many Public Relations have a rating of B...etc...

It appears i need to countif two columns but cant seem to get it to work...

Thanks in advance.

 

[Zathras]

Welcome to the forum! If you can't get someone to help you here, I don't know where else to go.

There are two main possibilities you can use:
1. Pivot Table
2. Database techniques

Both are described in the help file.

In either case, you need to use row 1 to contain column headings and begin placing your actual data with row 2. I have assumed the column headings of "SOURCE" "RATING" and "PHONE" (see below).

If it is really as simple as you described, a Pivot Table is probably what you want here. In order to use one effectively, I had to assume at least one more column in your layout: Let's say column C has a phone number. Then click in cell A1 (actually, anywhere in the data area), and select Data/Pivot Table... from the menu. Follow the prompts on the screen. I am assuming you have Office 2000. If you are using Excel 97, the steps are a little different, but not much.

When you get to Step 3 of 3, click the "Layout..." button. (Excel 97 puts you to the correct screen automatically.)

You should see a graphic with the words "ROW" "COLUMN" and "DATA" in rectangles. On the right-hand side you should see a button representing each column of your database. Using click-and-drag, drag "Source" to the ROW area, "Rating" to the COLUMN area and "Phone" to the DATA area. You should see the button text change to "Sum of Phone." Double-click the "Sum of Phone" button and change it to "Count of Phone" then click Ok. Click "Finish" and you should see the statistics you are looking for.

Let us know how it goes.

 

[TimboBee]

Ok I am off to the races, to see if I can figure this out.

Thanks for the reply,

 

[TimboBee]

Thanks Zathras,

Basically you gave the the excuse to delv into pivot tables which I simply have not had time to do. I played with them until I was comfortable with the results.

There does seem to be many things you can do with them so I'll have to go get a book on them.

The next step for me is VBA. Any suggestions for learning this vs sticking to formulas let me know.

Appreciate your help,

Timbobee