Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for multiple criteria, and return yet another
=SUMPRODUCT(--(A1:A100="Jones"),--(D1:D100="baseball"),E1:E100) or if Name is in AA1 and EventName is in AB1 =SUMPRODUCT(--(A1:A100=AA1),--(D1:D100=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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Search for multiple criteria, and return yet another
=SUMPRODUCT(--(A1:A100="Jones"),--(D1:D100="baseball"),E1:E100)
Not sure I understand the '--' before the arguments. Can you clarify? -- JMG "PCLIVE" wrote: =SUMPRODUCT(--(A1:A100="Jones"),--(D1:D100="baseball"),E1:E100) or if Name is in AA1 and EventName is in AB1 =SUMPRODUCT(--(A1:A100=AA1),--(D1:D100=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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
return multiple records matching multiple criteria | Excel Worksheet Functions | |||
Multiple search criteria to return one response | Excel Worksheet Functions | |||
How do I search excel spreadsheets using multiple search criteria. | Excel Worksheet Functions | |||
Search string with multiple criteria | Excel Worksheet Functions | |||
search multiple worksheets for an item and return the Wsheets name | Excel Worksheet Functions |