Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reference certain rows within array formulae? | Excel Worksheet Functions | |||
How to concatenate an array of four stings using formulae (not UDF) | Excel Worksheet Functions | |||
Array Problem | Excel Discussion (Misc queries) | |||
Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions | |||
OFFSET and array formulae | Excel Discussion (Misc queries) |