| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif with multiple criteria
I have a huge spreadsheet (sample below) and I need to know the value of what
2 people have sold. The problem is that the men are listed along the columns and the females are in the rows. I'm sure it's simple - I'm just mental blocking. The sort of results I'd expect a Debbie and Guy have sold $12,100/George and Rebecca have sold $3,000. A B C D E 1 Bob John Guy George 2 Jane $100 $600 $23,000 $2,000 3 Rebecca $200 $5,000 $43,000 $3,000 4 Alison $300 $8,000 $53,000 $400 5 Clair $400 $2,000 $70,000 $5,450 6 Debbie $800 $1,000 $12,100 $8,000 Thanks -- Pebbles |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif with multiple criteria
Hi,
Try this =INDEX(A1:E6, MATCH("Debbie",A1:A6,0), MATCH("Guy",A1:E1,0)) In practice I'd use cell references for the lookup values =INDEX(A1:E6, MATCH(F1,A1:A6,0), MATCH(G1,A1:E1,0)) When you change the range remember to change A1:E6 to the FULL table and A1:A6 - A1:E1 to the FULL column/row Mike "Pebbles" wrote: I have a huge spreadsheet (sample below) and I need to know the value of what 2 people have sold. The problem is that the men are listed along the columns and the females are in the rows. I'm sure it's simple - I'm just mental blocking. The sort of results I'd expect a Debbie and Guy have sold $12,100/George and Rebecca have sold $3,000. A B C D E 1 Bob John Guy George 2 Jane $100 $600 $23,000 $2,000 3 Rebecca $200 $5,000 $43,000 $3,000 4 Alison $300 $8,000 $53,000 $400 5 Clair $400 $2,000 $70,000 $5,450 6 Debbie $800 $1,000 $12,100 $8,000 Thanks -- Pebbles |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif with multiple criteria
Hi,
Because you looking for the intersect you could also do it like this =SUMPRODUCT((A2:A6=F1)*(B1:E1=G1)*(B2:E6)) Note now how the ranges are arranged. i.e A1 which is the top left corner of the table isn't now included in any of the ranges. Mike "Mike H" wrote: Hi, Try this =INDEX(A1:E6, MATCH("Debbie",A1:A6,0), MATCH("Guy",A1:E1,0)) In practice I'd use cell references for the lookup values =INDEX(A1:E6, MATCH(F1,A1:A6,0), MATCH(G1,A1:E1,0)) When you change the range remember to change A1:E6 to the FULL table and A1:A6 - A1:E1 to the FULL column/row Mike "Pebbles" wrote: I have a huge spreadsheet (sample below) and I need to know the value of what 2 people have sold. The problem is that the men are listed along the columns and the females are in the rows. I'm sure it's simple - I'm just mental blocking. The sort of results I'd expect a Debbie and Guy have sold $12,100/George and Rebecca have sold $3,000. A B C D E 1 Bob John Guy George 2 Jane $100 $600 $23,000 $2,000 3 Rebecca $200 $5,000 $43,000 $3,000 4 Alison $300 $8,000 $53,000 $400 5 Clair $400 $2,000 $70,000 $5,450 6 Debbie $800 $1,000 $12,100 $8,000 Thanks -- Pebbles |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif with multiple criteria
Mike
I could not get this to work but =INDIRECT(ADDRESS(MATCH(F1,A1:A6,0),MATCH(G1,A1:E1 ,0))) is ok Regards Peter "Mike H" wrote: Hi, Try this =INDEX(A1:E6, MATCH("Debbie",A1:A6,0), MATCH("Guy",A1:E1,0)) In practice I'd use cell references for the lookup values =INDEX(A1:E6, MATCH(F1,A1:A6,0), MATCH(G1,A1:E1,0)) When you change the range remember to change A1:E6 to the FULL table and A1:A6 - A1:E1 to the FULL column/row Mike "Pebbles" wrote: I have a huge spreadsheet (sample below) and I need to know the value of what 2 people have sold. The problem is that the men are listed along the columns and the females are in the rows. I'm sure it's simple - I'm just mental blocking. The sort of results I'd expect a Debbie and Guy have sold $12,100/George and Rebecca have sold $3,000. A B C D E 1 Bob John Guy George 2 Jane $100 $600 $23,000 $2,000 3 Rebecca $200 $5,000 $43,000 $3,000 4 Alison $300 $8,000 $53,000 $400 5 Clair $400 $2,000 $70,000 $5,450 6 Debbie $800 $1,000 $12,100 $8,000 Thanks -- Pebbles |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif with multiple criteria
Hi,
All the formula I posted work fine, did you edit them? Mike "Billy Liddel" wrote: Mike I could not get this to work but =INDIRECT(ADDRESS(MATCH(F1,A1:A6,0),MATCH(G1,A1:E1 ,0))) is ok Regards Peter "Mike H" wrote: Hi, Try this =INDEX(A1:E6, MATCH("Debbie",A1:A6,0), MATCH("Guy",A1:E1,0)) In practice I'd use cell references for the lookup values =INDEX(A1:E6, MATCH(F1,A1:A6,0), MATCH(G1,A1:E1,0)) When you change the range remember to change A1:E6 to the FULL table and A1:A6 - A1:E1 to the FULL column/row Mike "Pebbles" wrote: I have a huge spreadsheet (sample below) and I need to know the value of what 2 people have sold. The problem is that the men are listed along the columns and the females are in the rows. I'm sure it's simple - I'm just mental blocking. The sort of results I'd expect a Debbie and Guy have sold $12,100/George and Rebecca have sold $3,000. A B C D E 1 Bob John Guy George 2 Jane $100 $600 $23,000 $2,000 3 Rebecca $200 $5,000 $43,000 $3,000 4 Alison $300 $8,000 $53,000 $400 5 Clair $400 $2,000 $70,000 $5,450 6 Debbie $800 $1,000 $12,100 $8,000 Thanks -- Pebbles |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif with multiple criteria
Hi Mike
I couldn't paste this in one cell so I had to rewrite it. I'll look at it again. But I'd use the SUMPRODUCT anyway - that was fine Thanks Peter "Mike H" wrote: Hi, All the formula I posted work fine, did you edit them? Mike "Billy Liddel" wrote: Mike I could not get this to work but =INDIRECT(ADDRESS(MATCH(F1,A1:A6,0),MATCH(G1,A1:E1 ,0))) is ok Regards Peter "Mike H" wrote: Hi, Try this =INDEX(A1:E6, MATCH("Debbie",A1:A6,0), MATCH("Guy",A1:E1,0)) In practice I'd use cell references for the lookup values =INDEX(A1:E6, MATCH(F1,A1:A6,0), MATCH(G1,A1:E1,0)) When you change the range remember to change A1:E6 to the FULL table and A1:A6 - A1:E1 to the FULL column/row Mike "Pebbles" wrote: I have a huge spreadsheet (sample below) and I need to know the value of what 2 people have sold. The problem is that the men are listed along the columns and the females are in the rows. I'm sure it's simple - I'm just mental blocking. The sort of results I'd expect a Debbie and Guy have sold $12,100/George and Rebecca have sold $3,000. A B C D E 1 Bob John Guy George 2 Jane $100 $600 $23,000 $2,000 3 Rebecca $200 $5,000 $43,000 $3,000 4 Alison $300 $8,000 $53,000 $400 5 Clair $400 $2,000 $70,000 $5,450 6 Debbie $800 $1,000 $12,100 $8,000 Thanks -- Pebbles |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif with multiple criteria
The easiest way of doing it is "Defined Names ranges" in both vertical and
horizontal data range Formula: = Debbie Guy "Pebbles" wrote: I have a huge spreadsheet (sample below) and I need to know the value of what 2 people have sold. The problem is that the men are listed along the columns and the females are in the rows. I'm sure it's simple - I'm just mental blocking. The sort of results I'd expect a Debbie and Guy have sold $12,100/George and Rebecca have sold $3,000. A B C D E 1 Bob John Guy George 2 Jane $100 $600 $23,000 $2,000 3 Rebecca $200 $5,000 $43,000 $3,000 4 Alison $300 $8,000 $53,000 $400 5 Clair $400 $2,000 $70,000 $5,450 6 Debbie $800 $1,000 $12,100 $8,000 Thanks -- Pebbles |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
| Multiple Criteria for SUMIF | Excel Worksheet Functions | |||
| SUMIF and {multiple criteria} | Excel Discussion (Misc queries) | |||
| SUMIF with multiple criteria | Excel Discussion (Misc queries) | |||
| sumif multiple criteria | Excel Worksheet Functions | |||
Linear Mode
