Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 12th 08, 08:56 PM posted to microsoft.public.excel.worksheet.functions
JMG JMG is offline
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 2
Default Search for multiple criteria, and return yet another

Basic output from 3rd party database is .xls in a format similar to:

Name OtherData OtherData EventName PointsEarned
Jones xxxxx xxxxx baseball 2.0
Jones xxxxx xxxxx football 2.5
Jones xxxxx xxxxx soccer 3.0
Smith xxxxx xxxxx baseball 1.0
Smith xxxxx xxxxx football 1.1
Smith xxxxx xxxxx soccer 1.5
White...and so on

My Goal:
Lookup by Name and EventName, and return PointsEarned.

BASEBALL FOOTBALL SOCCER

Jones 2.0 2.5 3.0
Smith 1.0 1.1 1.5
White...

I also totally lack understanding of PivotTables, and out of pure
stubbornness, am trying to avoid using them. ...probably to my own demise.

Thanks in advance.
--
JMG

  #2   Report Post  
Old March 12th 08, 09:03 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 1,311
Default Search for multiple criteria, and return yet another


=SUMPRODUCT(--(A1:A100="Jones"),--(D1100="baseball"),E1:E100)

or if Name is in AA1
and EventName is in AB1

=SUMPRODUCT(--(A1:A100=AA1),--(D1100=AB1),E1:E100)


HTH,
Paul

--

"JMG" wrote in message
...
Basic output from 3rd party database is .xls in a format similar to:

Name OtherData OtherData EventName PointsEarned
Jones xxxxx xxxxx baseball 2.0
Jones xxxxx xxxxx football 2.5
Jones xxxxx xxxxx soccer 3.0
Smith xxxxx xxxxx baseball 1.0
Smith xxxxx xxxxx football 1.1
Smith xxxxx xxxxx soccer 1.5
White...and so on

My Goal:
Lookup by Name and EventName, and return PointsEarned.

BASEBALL FOOTBALL SOCCER

Jones 2.0 2.5 3.0
Smith 1.0 1.1 1.5
White...

I also totally lack understanding of PivotTables, and out of pure
stubbornness, am trying to avoid using them. ...probably to my own
demise.

Thanks in advance.
--
JMG



  #3   Report Post  
Old March 12th 08, 09:04 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,118
Default Search for multiple criteria, and return yet another

EASY....Pivot Table!....Here's how:

From the Excel Main Menu: <Data<Pivot Table
Use: Excel..Click [Next]
Select your data..Click [Next]
Click the [Layout] button

ROW: Drag the Name field here
COLUMN: Drag the EventName field here
DATA: Drag the PointsEarned field here
If it doesn't list as Sum of PointsEarned...dbl-click it and select Sum
Click [OK]
Select where you want the Pivot Table.Click [Finish].

That will create a table showing with:
Names down the left side
EventNames across the top
Sum of PointsEarnedand at each intersection

To refresh the Pivot Table, just right click it and select Refresh Data

Pivot Table Links:
http://www.nickhodge.co.uk/gui/datam...ablereport.htm
http://www.contextures.com/tiptech.html


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"JMG" wrote in message
...
Basic output from 3rd party database is .xls in a format similar to:

Name OtherData OtherData EventName PointsEarned
Jones xxxxx xxxxx baseball 2.0
Jones xxxxx xxxxx football 2.5
Jones xxxxx xxxxx soccer 3.0
Smith xxxxx xxxxx baseball 1.0
Smith xxxxx xxxxx football 1.1
Smith xxxxx xxxxx soccer 1.5
White...and so on

My Goal:
Lookup by Name and EventName, and return PointsEarned.

BASEBALL FOOTBALL SOCCER

Jones 2.0 2.5 3.0
Smith 1.0 1.1 1.5
White...

I also totally lack understanding of PivotTables, and out of pure
stubbornness, am trying to avoid using them. ...probably to my own
demise.

Thanks in advance.
--
JMG





  #4   Report Post  
Old March 12th 08, 09:10 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2007
Posts: 11,501
Default Search for multiple criteria, and return yet another

Hi,

I've assumed your table is in coilumns A,B,C,D & E.

Make a table like this

Baseball Football Soccer
Jones 2 2.5 3
Smith 2 2.2 2.5
White 0 0 0

At the intersect of Jones/Baseball put this formula
=SUMPRODUCT(($A$2:$A$7=$I2)*($D$2:$D$7=J$1)*($E$2: $E$7))
Drag down and then across to get the numbers above
Note my table is in columns
I,J,K & L
Mike


"JMG" wrote:

Basic output from 3rd party database is .xls in a format similar to:

Name OtherData OtherData EventName PointsEarned
Jones xxxxx xxxxx baseball 2.0
Jones xxxxx xxxxx football 2.5
Jones xxxxx xxxxx soccer 3.0
Smith xxxxx xxxxx baseball 1.0
Smith xxxxx xxxxx football 1.1
Smith xxxxx xxxxx soccer 1.5
White...and so on

My Goal:
Lookup by Name and EventName, and return PointsEarned.

BASEBALL FOOTBALL SOCCER

Jones 2.0 2.5 3.0
Smith 1.0 1.1 1.5
White...

I also totally lack understanding of PivotTables, and out of pure
stubbornness, am trying to avoid using them. ...probably to my own demise.

Thanks in advance.
--
JMG

  #5   Report Post  
Old March 12th 08, 09:24 PM posted to microsoft.public.excel.worksheet.functions
JMG JMG is offline
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 2
Default Search for multiple criteria, and return yet another

=SUMPRODUCT(--(A1:A100="Jones"),--(D1100="baseball"),E1:E100)

Not sure I understand the '--' before the arguments. Can you clarify?

--
JMG


"PCLIVE" wrote:


=SUMPRODUCT(--(A1:A100="Jones"),--(D1100="baseball"),E1:E100)

or if Name is in AA1
and EventName is in AB1

=SUMPRODUCT(--(A1:A100=AA1),--(D1100=AB1),E1:E100)


HTH,
Paul

--

"JMG" wrote in message
...
Basic output from 3rd party database is .xls in a format similar to:

Name OtherData OtherData EventName PointsEarned
Jones xxxxx xxxxx baseball 2.0
Jones xxxxx xxxxx football 2.5
Jones xxxxx xxxxx soccer 3.0
Smith xxxxx xxxxx baseball 1.0
Smith xxxxx xxxxx football 1.1
Smith xxxxx xxxxx soccer 1.5
White...and so on

My Goal:
Lookup by Name and EventName, and return PointsEarned.

BASEBALL FOOTBALL SOCCER

Jones 2.0 2.5 3.0
Smith 1.0 1.1 1.5
White...

I also totally lack understanding of PivotTables, and out of pure
stubbornness, am trying to avoid using them. ...probably to my own
demise.

Thanks in advance.
--
JMG






  #6   Report Post  
Old March 12th 08, 09:33 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,118
Default Search for multiple criteria, and return yet another

You'll notice I ignored your comment about avoiding pivot tables. <g
This one is SO easy you almost HAVE TO use one!

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Ron Coderre" wrote in message
...
EASY....Pivot Table!....Here's how:

From the Excel Main Menu: <Data<Pivot Table
Use: Excel..Click [Next]
Select your data..Click [Next]
Click the [Layout] button

ROW: Drag the Name field here
COLUMN: Drag the EventName field here
DATA: Drag the PointsEarned field here
If it doesn't list as Sum of PointsEarned...dbl-click it and select Sum
Click [OK]
Select where you want the Pivot Table.Click [Finish].

That will create a table showing with:
Names down the left side
EventNames across the top
Sum of PointsEarnedand at each intersection

To refresh the Pivot Table, just right click it and select Refresh Data

Pivot Table Links:
http://www.nickhodge.co.uk/gui/datam...ablereport.htm
http://www.contextures.com/tiptech.html


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"JMG" wrote in message
...
Basic output from 3rd party database is .xls in a format similar to:

Name OtherData OtherData EventName PointsEarned
Jones xxxxx xxxxx baseball 2.0
Jones xxxxx xxxxx football 2.5
Jones xxxxx xxxxx soccer 3.0
Smith xxxxx xxxxx baseball 1.0
Smith xxxxx xxxxx football 1.1
Smith xxxxx xxxxx soccer 1.5
White...and so on

My Goal:
Lookup by Name and EventName, and return PointsEarned.

BASEBALL FOOTBALL SOCCER

Jones 2.0 2.5 3.0
Smith 1.0 1.1 1.5
White...

I also totally lack understanding of PivotTables, and out of pure
stubbornness, am trying to avoid using them. ...probably to my own
demise.

Thanks in advance.
--
JMG











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
return multiple records matching multiple criteria Karthik Excel Worksheet Functions 2 March 22nd 06 05:42 PM
Multiple search criteria to return one response Dan Excel Worksheet Functions 3 March 3rd 06 04:44 AM
How do I search excel spreadsheets using multiple search criteria. Kasper Excel Worksheet Functions 4 December 15th 05 01:26 AM
Search string with multiple criteria fLiPMoD£ Excel Worksheet Functions 2 May 5th 05 08:02 PM
search multiple worksheets for an item and return the Wsheets name Chris Excel Worksheet Functions 16 November 7th 04 01:15 PM


All times are GMT +1. The time now is 02:36 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright ©2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017