Array formulae - problem
 

I have 3 columns of data in a worksheet called "data". the
information in this wksheet is organised as follows:

CC Nom Amount
KABC 4101 500
KABQ 5101 700
KABC 4102 450


Now, I would like to write an array formulae/sum product what would up
all the nominal codes in 4101-4107 series when the code KABC is typed
in cell a2. I have tried this, it wont capture the "41" range:
=SUMPRODUCT(--('[May 07.xls]Actuals'!$A$2:$A$20000=$a$2),--('[May
07.xls]Actuals'!$B$2:$B$20000=$B7),('[May 07.xls]Actuals'!$C$2:$C
$20000)). The fault with this formulae is that it cant group specific
codes and report the amount. Can anyone help please.

Many thanks,
Ola.

Array formulae - problem
 

What are you currently getting from the existing formula (below)?
What is in cell $B7?

=SUMPRODUCT(--('[May 07.xls]Actuals'!$A$2:$A$20000=$a$2),--('[May
07.xls]Actuals'!$B$2:$B$20000=$B7),('[May 07.xls]Actuals'!$C$2:$C
$20000)).

Couple suggestion (shots in the dark)..try modified like so

=SUMPRODUCT(--('[May 07.xls]Actuals'!$A$2:$A$20000='Data'!$a$2),--(Left('[May
07.xls]Actuals'!$B$2:$B$20000,2)="41"),('[May 07.xls]Actuals'!$C$2:$C
$20000)).



"Ola2B" wrote:

I have 3 columns of data in a worksheet called "data". the
information in this wksheet is organised as follows:

CC Nom Amount
KABC 4101 500
KABQ 5101 700
KABC 4102 450


Now, I would like to write an array formulae/sum product what would up
all the nominal codes in 4101-4107 series when the code KABC is typed
in cell a2. I have tried this, it wont capture the "41" range:
=SUMPRODUCT(--('[May 07.xls]Actuals'!$A$2:$A$20000=$a$2),--('[May
07.xls]Actuals'!$B$2:$B$20000=$B7),('[May 07.xls]Actuals'!$C$2:$C
$20000)). The fault with this formulae is that it cant group specific
codes and report the amount. Can anyone help please.

Many thanks,
Ola.

Array formulae - problem
 

Try:

=SUMPRODUCT(--(LEFT(B2:B4,2)="41"),--(A2:A4="KABC"),C2:C4)

=SUMPRODUCT(--('[May 07.xls]Actuals'!$A$2:$A$20000=$A$2),--(LEFT('[May
07.xls]Actuals'!$B$2:$B$20000,2)=$B7),('[May 07.xls]Actuals'!$C$2:$C
$20000))

for "41" range.

"Ola2B" wrote:

I have 3 columns of data in a worksheet called "data". the
information in this wksheet is organised as follows:

CC Nom Amount
KABC 4101 500
KABQ 5101 700
KABC 4102 450


Now, I would like to write an array formulae/sum product what would up
all the nominal codes in 4101-4107 series when the code KABC is typed
in cell a2. I have tried this, it wont capture the "41" range:
=SUMPRODUCT(--('[May 07.xls]Actuals'!$A$2:$A$20000=$a$2),--('[May
07.xls]Actuals'!$B$2:$B$20000=$B7),('[May 07.xls]Actuals'!$C$2:$C
$20000)). The fault with this formulae is that it cant group specific
codes and report the amount. Can anyone help please.

Many thanks,
Ola.

Array formulae - problem
 

You do not tell us what you have in B2.
This worked for me in a more limited setting that your problem:
a) in B2 enter 41
b) modify formula to read
=SUMPRODUCT(--('[May 07.xls]Actuals'!$A$2:$A$20000=$a$2),
--(LEFT('[May 07.xls]Actuals'!$B$2:$B$20000,2)=TEXT($B7,"@@"),
('[May 07.xls]Actuals'!$C$2:$C$20000)).
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Ola2B" wrote in message
ups.com...
I have 3 columns of data in a worksheet called "data". the
information in this wksheet is organised as follows:

CC Nom Amount
KABC 4101 500
KABQ 5101 700
KABC 4102 450


Now, I would like to write an array formulae/sum product what would up
all the nominal codes in 4101-4107 series when the code KABC is typed
in cell a2. I have tried this, it wont capture the "41" range:
=SUMPRODUCT(--('[May 07.xls]Actuals'!$A$2:$A$20000=$a$2),--('[May
07.xls]Actuals'!$B$2:$B$20000=$B7),('[May 07.xls]Actuals'!$C$2:$C
$20000)). The fault with this formulae is that it cant group specific
codes and report the amount. Can anyone help please.

Many thanks,
Ola.

Array formulae - problem
 

On 21 Jun, 12:13, JMay wrote:
What are you currently getting from the existing formula (below)?
What is in cell $B7?

=SUMPRODUCT(--('[May 07.xls]Actuals'!$A$2:$A$20000=$a$2),--('[May
07.xls]Actuals'!$B$2:$B$20000=$B7),('[May 07.xls]Actuals'!$C$2:$C
$20000)).

Couple suggestion (shots in the dark)..try modified like so

=SUMPRODUCT(--('[May 07.xls]Actuals'!$A$2:$A$20000='Data'!$a$2),--(Left('[May
07.xls]Actuals'!$B$2:$B$20000,2)="41"),('[May 07.xls]Actuals'!$C$2:$C
$20000)).



"Ola2B" wrote:
I have 3 columns of data in a worksheet called "data". the
information in this wksheet is organised as follows:

CC Nom Amount
KABC 4101 500
KABQ 5101 700
KABC 4102 450

Now, I would like to write an array formulae/sum product what would up
all the nominal codes in 4101-4107 series when the code KABC is typed
in cell a2. I have tried this, it wont capture the "41" range:
=SUMPRODUCT(--('[May 07.xls]Actuals'!$A$2:$A$20000=$a$2),--('[May
07.xls]Actuals'!$B$2:$B$20000=$B7),('[May 07.xls]Actuals'!$C$2:$C
$20000)). The fault with this formulae is that it cant group specific
codes and report the amount. Can anyone help please.

Many thanks,
Ola.- Hide quoted text -

- Show quoted text -

Many thanks. It did work.