| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple match statements?
Hi,
I am trying to match 3 data points and return the value for the cell they intersect at - without using concatenate if possible. In the below table I would like to return the value for Product A, Country UK, Week 2 i.e. 20 - any ideas? Product Country Week Qty A UK 1 10 A UK 2 20 A UK 3 15 A FR 1 10 A FR 2 12 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple match statements?
Try this:
=SUMPRODUCT(--(product="A"),--(country="UK"),--(week=2),qty) Biff "Jas" wrote in message ... Hi, I am trying to match 3 data points and return the value for the cell they intersect at - without using concatenate if possible. In the below table I would like to return the value for Product A, Country UK, Week 2 i.e. 20 - any ideas? Product Country Week Qty A UK 1 10 A UK 2 20 A UK 3 15 A FR 1 10 A FR 2 12 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple match statements?
SUMPRODUCT would do it.
-- David Biddulph "Jas" wrote in message ... Hi, I am trying to match 3 data points and return the value for the cell they intersect at - without using concatenate if possible. In the below table I would like to return the value for Product A, Country UK, Week 2 i.e. 20 - any ideas? Product Country Week Qty A UK 1 10 A UK 2 20 A UK 3 15 A FR 1 10 A FR 2 12 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple match statements?
Sorry I was not complete and clear in request, Product and country are
dynamic based on user input, so I have linked them to the location where input, then I want the formula to use this input against the table of 228k rows and find the value at that intersection - make sense? "T. Valko" wrote: Try this: =SUMPRODUCT(--(product="A"),--(country="UK"),--(week=2),qty) Biff "Jas" wrote in message ... Hi, I am trying to match 3 data points and return the value for the cell they intersect at - without using concatenate if possible. In the below table I would like to return the value for Product A, Country UK, Week 2 i.e. 20 - any ideas? Product Country Week Qty A UK 1 10 A UK 2 20 A UK 3 15 A FR 1 10 A FR 2 12 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple match statements?
Sorry you were both right it worked, needed to read it a bit more carefully -
more learnings :) "David Biddulph" wrote: SUMPRODUCT would do it. -- David Biddulph "Jas" wrote in message ... Hi, I am trying to match 3 data points and return the value for the cell they intersect at - without using concatenate if possible. In the below table I would like to return the value for Product A, Country UK, Week 2 i.e. 20 - any ideas? Product Country Week Qty A UK 1 10 A UK 2 20 A UK 3 15 A FR 1 10 A FR 2 12 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple match statements?
A1 = product = A
A2 = country = UK =SUMPRODUCT(--(product=A1),--(country=A2),--(week=2),qty) the table of 228k rows I'd imagine that any formula will be slow to calculate on that size range. I don't have Excel 2007, maybe the COUNTIFS function would be faster. Biff "Jas" wrote in message ... Sorry I was not complete and clear in request, Product and country are dynamic based on user input, so I have linked them to the location where input, then I want the formula to use this input against the table of 228k rows and find the value at that intersection - make sense? "T. Valko" wrote: Try this: =SUMPRODUCT(--(product="A"),--(country="UK"),--(week=2),qty) Biff "Jas" wrote in message ... Hi, I am trying to match 3 data points and return the value for the cell they intersect at - without using concatenate if possible. In the below table I would like to return the value for Product A, Country UK, Week 2 i.e. 20 - any ideas? Product Country Week Qty A UK 1 10 A UK 2 20 A UK 3 15 A FR 1 10 A FR 2 12 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple match statements?
maybe the COUNTIFS function would be faster.
Check that! You're not counting. Is there a SUMIFS function? Biff "T. Valko" wrote in message ... A1 = product = A A2 = country = UK =SUMPRODUCT(--(product=A1),--(country=A2),--(week=2),qty) the table of 228k rows I'd imagine that any formula will be slow to calculate on that size range. I don't have Excel 2007, maybe the COUNTIFS function would be faster. Biff "Jas" wrote in message ... Sorry I was not complete and clear in request, Product and country are dynamic based on user input, so I have linked them to the location where input, then I want the formula to use this input against the table of 228k rows and find the value at that intersection - make sense? "T. Valko" wrote: Try this: =SUMPRODUCT(--(product="A"),--(country="UK"),--(week=2),qty) Biff "Jas" wrote in message ... Hi, I am trying to match 3 data points and return the value for the cell they intersect at - without using concatenate if possible. In the below table I would like to return the value for Product A, Country UK, Week 2 i.e. 20 - any ideas? Product Country Week Qty A UK 1 10 A UK 2 20 A UK 3 15 A FR 1 10 A FR 2 12 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple match statements?
Yes Biff, there is
=SUMIFS(D:D,A:A="A",B:B,"UK",C:C,2) Note that unlike Sumif, the range to be totalled comes first. -- Regards Roger Govier "T. Valko" wrote in message ... maybe the COUNTIFS function would be faster. Check that! You're not counting. Is there a SUMIFS function? Biff "T. Valko" wrote in message ... A1 = product = A A2 = country = UK =SUMPRODUCT(--(product=A1),--(country=A2),--(week=2),qty) the table of 228k rows I'd imagine that any formula will be slow to calculate on that size range. I don't have Excel 2007, maybe the COUNTIFS function would be faster. Biff "Jas" wrote in message ... Sorry I was not complete and clear in request, Product and country are dynamic based on user input, so I have linked them to the location where input, then I want the formula to use this input against the table of 228k rows and find the value at that intersection - make sense? "T. Valko" wrote: Try this: =SUMPRODUCT(--(product="A"),--(country="UK"),--(week=2),qty) Biff "Jas" wrote in message ... Hi, I am trying to match 3 data points and return the value for the cell they intersect at - without using concatenate if possible. In the below table I would like to return the value for Product A, Country UK, Week 2 i.e. 20 - any ideas? Product Country Week Qty A UK 1 10 A UK 2 20 A UK 3 15 A FR 1 10 A FR 2 12 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| INDEX / MATCH with SUMIF/COUNTIF/SUMPRODUCT Statements? | Excel Worksheet Functions | |||
| match statements | Excel Discussion (Misc queries) | |||
| Multiple if statements with multiple conditions | Excel Discussion (Misc queries) | |||
| Match/Lookup Statements | Excel Worksheet Functions | |||
| Data Validation w/ If, Match & Index Statements | Excel Worksheet Functions | |||
Linear Mode
