Thread: Function to match and then count

Let me explain.
I have two worksheets in my excell workbook.
One sheet we will call ApplicationName
second sheet we will ImportTable.

In Sheet 1 (ApplicationName) I have:
<column1> . <column 2> (using Countif function)
MSWORD . Total# of servers from ImportTable where MS Word is there
MS Excell . Total# of servers from ImportTable where MS Excell is there
MS PPS . Total# of servers from ImportTable where MS PPS is there


In sheet 2 - (ImportTable) I have
<column1> . <column2> . <column3>
ServerA . MSWORD . Level0
ServerB . MSWORD . Level0
ServerC . MS Excell . Level1
ServerD . MSPPS . Level0
ServerE . MSPPS . Level1
ServerF . MSWORD . Level2
ServerG . MSExcell . Level2
ServerH . MSWORD . Level1
ServerI . MS PPS . Level2.
.
Now what I want to do is the following:
In Sheet1 - Application - I want to add a column that will match the Application name and then count the number of Level0's assigned and another column where the same criteria counting Level1 etc..
So that I can see a breakdown of how many level0, level1, and level2's are there for each application.
I am using 2010
Thoughts??

Hi

Does the attached Pivot Table provide what you want?

In 2010, that sounds like a case for COUNTIFS

Regards

Originally Posted by Jointheir7

yes it does .. however how was it done??

Hi
On the data page place your cursor within your data, having ensured you have a header row as I did.
Insert tab>Table>my table has headers
On the Design tab that appears, Summarise with Pivot Table>OK

On the new sheet that appears, from the Field list
drag Product to the Row labels area
drag Server to the Row Labels area
drag Level to the Column label area
and finally drag Product once again to the Data area

For more information on Pivot Table take a look at Debra Dalgleish's site - she has lots of tutorials
http://www.contextures.com/CreatePivotTable.html

Using the Data sheet that Roger provided:

HTML Code: 

		(I)	(J)	(K)	(L)
		Level0	Level1	Level2	
(3)	MSWORD	2	1	1	4
(4)	MSExcell	0	1	1	2
(5)	MSPPS	1	1	1	3
(6)		3	3	3	
					
	Cell I3:	=COUNTIFS($B$2:$B$10,$H3,$C$2:$C$10,I$2)			
					

Drag the formula across and down.

Regards