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.

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.

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.

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.

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.