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

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

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

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

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

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

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

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