Query - is COUNTIF correct here?

[AndyTH]

Hello,

I am working on an Excel spreadsheet involving formula to produce statistics for a manager and have a query that I would appreciate some advice on.

Scenario

The spreadsheet has multiple tabs, one of which is a huge export of the year’s completed calls from our database. I have been tasked with creating a spreadsheet to show the number of calls we completed (sorted by various criteria) for the different types of school we serve. The first tab is going to be a sort of front cover, giving the essential breakdown of numbers. Firstly:

Total Calls closed for: Primary Schools, Secondary Schools, and Special Schools.

I was able to do this easily, as the data in the export tab only shows closed calls, so I just did a COUNTIF formula and set it to the school’s name. However, I have encountered a problem with the next step:


We support a variety of different programs for the schools, which is also reflected in the data exported from the helpdesk database. I would like to show the total calls completed for each type of school, in a particular program. For Example:

Total Microsoft Word calls logged by Secondary schools.


Initially I tried applying the COUNTIF formula to both of the columns of data and specifying there, i.e.

Range - A6:B17
Criteria – Secondary, EXCEL

But this did not produce a coherent result. Am I making a basic mistake or perhaps the wrong formula?

Any advice that you could give would be greatly appreciated.

Many thanks.

[darkyam]

Sumproduct would work. =Sumproduct((A6:A17="Secondary")*(B6:B17="Excel"))

[romperstomper]

It sounds to me as though a pivot table would be ideal for what you are doing, BTW.

[AndyTH]

Sumproduct would work. =Sumproduct((A6:A17="Secondary")*(B6:B17="Excel"))

Many thanks for the 2 replies so far. Unfortunately I think Pivot Tables are a little beyond my ken at the moment, but if things get dire I'll have a read about them.

The formula that was suggested to me above returns an error of #NUM. Is this because there are no actual numbers involved? The data I have is effectively thousands of rows of data as each case is logged, specific to this query an example could be:

Harrison Primary School | Microsoft Excel | Primary School
Andrew Secondary School | Dreamweaver | Secondary School
Thomas Special Education School | Microsoft Publisher | Special School


So there are no actual numbers involved. I want a formula that I can apply to the columns holding the information for Primary / Secondary / Special school and the Package the call was related to, so that I can change the criteria accordingly to display a total number of calls we received from Primary Schools for Microsoft Excel, for example.


Sorry if I worded my original post wrong or if i'm just entering the formula provided , incorrectly.

many thanks.

[romperstomper]

It does not require numbers. How did you actually enter your formula? (we need the exact formula you used)

[AndyTH]

As i've applied the formula to the context of what i'm doing it now reads:

=SUMPRODUCT((Data!P:P="Sims Upgrade")*(Data!Z:Z="Primary"))

P being the column that has the Application Name, Z being if the school is a Primary / Secondary / Special.

Thanks

[romperstomper]

In versions of Excel prior to 2007, you cannot use entire column references in a SUMPRODUCT (or array) formula. You will have to specify row numbers (even if it's 1:65535).

[AndyTH]

That's worked, fantastic thank you so much for all your help and patience, it's very much appreciated.