TechArena Community Excel: Using multiple criteria

#1
09-04-2009
 Member Join Date: Jan 2009 Posts: 11
Excel: Using multiple criteria

I have been trying to do a little counting a lot of talk but little trouble finding the right function

The situation is as follows:

I have 3 columns.
The first column there are various names, Lasse, Jens, Soren
In the second column, there are different sports, eg football and handball
In the third column are listed a number of points he has made in that sport.
(a person can easily occur multiple times in each sport)

What I would like it to have to count all points along the criteria "Lasse" is included in column 1 and "football" is included in column 2

I have so far spent sum.hvis a lot of other things but can not find out whether it can help when multiple criteria must be met

Hope there is a helpful excel shark out there
#2
09-04-2009
 Member Join Date: May 2008 Posts: 4,829
Re: Excel: Using multiple criteria

You should be able to create a pivot table of your data, and then structure the following columns 1 and 2 in rows and the sum of your score values.

otherwise you enter in something SUMIFS = () lark
= SUMIFS ([column with your point], [column with your name], [name you are searching for] "Lasse" (possibly cellular); [column with your sports], [name of your sport] "football" ( possible. cell))
#3
09-04-2009
 Member Join Date: Jan 2009 Posts: 11
Re: Excel: Using multiple criteria

I have unfortunately not tracked PivotTable yet. Furthermore, I am using Excel 2002 in spanish as function sumifs () does not exist or I just cannot find the correct translation.
#4
09-04-2009
 Member Join Date: May 2008 Posts: 4,829
Re: Excel: Using multiple criteria

you do not have a grip on the PivotTable to use them.
Select your 3 columns, go into paste and make a new pivot table.
So put your names into first, and so your sports in the left side.
Then take your points and apply into right.

#5
09-04-2009
 Member Join Date: Jan 2009 Posts: 11
Re: Excel: Using multiple criteria

Well I found out that I could use the following
SUMPRODUCT((A1:A60="Lasse")*(B1:B6
0="Fodbold")*C1:C60)

My thanks to those who have made proposals for the solution.

And then my job as to have upgraded their excel version.
#6
09-04-2009
 Member Join Date: May 2008 Posts: 4,339
Re: Excel: Using multiple criteria

Quote:
 Originally Posted by Quily And then my job as to have upgraded their excel version.
Yes for an English. It is completely ridiculous that function names are also translated. So the world with illiterate IT people can not do it.

 Tags: ms excel