I am trying to Sum all the data in COLUMN H that meets ALL my
specifications.

Column B data must start with 313- or 312-
Column K contains CT08
Column R contains B60

I am having trouble with the AND/OR logic in column B... here is the
formula without the OR test...

SUMPRODUCT(--ISNUMBER(SEARCH("313-*",'Sheet1'!$B$2:$B$60000)),--('Sheet1
'!$K$2:$K$60000="*ct08*"),--('Sheet1'!$R$2:$R$60000="B60"),
'Sheet1'!$H$2:$H$60000)

thanks all

*** Sent via Developersdex http://www.developersdex.com ***

=SUMPRODUCT(--(OR(LEFT(Sheet1!$B$2:$B$60000,3)="312",LEFT(Sheet1 !$B$2:$B$60000,3)="313")),--(Sheet1!$K$2:$K$60000="*ct08*"),--(Sheet1!$R$2:$R$60000="B60"),Sheet1!$H$2:$H$60000)

"Kirill V" wrote:

I am trying to Sum all the data in COLUMN H that meets ALL my
specifications.

Column B data must start with 313- or 312-
Column K contains CT08
Column R contains B60

I am having trouble with the AND/OR logic in column B... here is the
formula without the OR test...

SUMPRODUCT(--ISNUMBER(SEARCH("313-*",'Sheet1'!$B$2:$B$60000)),--('Sheet1
'!$K$2:$K$60000="*ct08*"),--('Sheet1'!$R$2:$R$60000="B60"),
'Sheet1'!$H$2:$H$60000)

thanks all

*** Sent via Developersdex http://www.developersdex.com ***

Hi,

maybe this array formula

=COUNT(IF(LEFT(Sheet1!B2:B6000,3)="313",IF(NOT(ISE RROR(SEARCH("CT08",Sheet1!K2:K6000))),IF(Sheet1!R2 :R6000="B60",1))))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Kirill V" wrote:

I am trying to Sum all the data in COLUMN H that meets ALL my
specifications.

Column B data must start with 313- or 312-
Column K contains CT08
Column R contains B60

I am having trouble with the AND/OR logic in column B... here is the
formula without the OR test...

SUMPRODUCT(--ISNUMBER(SEARCH("313-*",'Sheet1'!$B$2:$B$60000)),--('Sheet1
'!$K$2:$K$60000="*ct08*"),--('Sheet1'!$R$2:$R$60000="B60"),
'Sheet1'!$H$2:$H$60000)

thanks all

*** Sent via Developersdex http://www.developersdex.com ***

Hi,

Ignore my last effort, i looked at your formula instead of the text. Try
this instead


=SUM(IF(OR(LEFT(Sheet1!B2:B6000,3)="313",LEFT(Shee t1!B2:B6000,3)="314"),IF(NOT(ISERROR(SEARCH("CT08" ,Sheet1!K2:K6000))),IF(Sheet1!R2:R6000="B60",Sheet 1!H2:H6000))))

Once again array entered.

Mike

"Mike H" wrote:

Hi,

maybe this array formula

=COUNT(IF(LEFT(Sheet1!B2:B6000,3)="313",IF(NOT(ISE RROR(SEARCH("CT08",Sheet1!K2:K6000))),IF(Sheet1!R2 :R6000="B60",1))))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Kirill V" wrote:

I am trying to Sum all the data in COLUMN H that meets ALL my
specifications.

Column B data must start with 313- or 312-
Column K contains CT08
Column R contains B60

I am having trouble with the AND/OR logic in column B... here is the
formula without the OR test...

SUMPRODUCT(--ISNUMBER(SEARCH("313-*",'Sheet1'!$B$2:$B$60000)),--('Sheet1
'!$K$2:$K$60000="*ct08*"),--('Sheet1'!$R$2:$R$60000="B60"),
'Sheet1'!$H$2:$H$60000)

thanks all

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Mike... for some reason pulls in Zero. Just to clarify, all my
contitions need to be met in the SUM. Column B has to begin with 312, or
313, Column K must contain CT08 and Column R must contain B60... only
then sum column H.

You can copy & Paste the below into excel... thats my sample.

Ldgr Account Period Trans Date Journal Line Base Amt Base Amt
(+/-) D/C Reference Description
A 410-100250 2009002 20090213 AAA 112 0.02 0.02 D CC CT0360
A 415-000000 2009002 20090217 AAA 112 0.02 0.02 D CC CT0360
A 312-000000 2009002 20090217 AAA 112 0.02 -0.02 C CC CT0860
A 313-000000 2009002 20090202 AAA 112 43290 43290 D CC CT0360
A 313-000000 2009002 20090202 AAA 112 279542.6 -279542.6 C CC CT0360
A 313-000000 2009002 20090202 AAA 112 517.08 517.08 D CC CT0360
A 313-000000 2009002 20090202 AAA 112 1165946.73 1165946.73 D CC CT0360
A 313-000000 2009002 20090202 AAA 112 436670.7 -436670.7 C CC CT0360
A 313-000000 2009002 20090202 AAA 112 87377.74 -87377.74 C CC CT0860
A 313-000000 2009002 20090202 AAA 112 631312.56 -631312.56 C CC CT0360
A 313-000000 2009002 20090202 AAA 112 61631.5 -61631.5 C CC CT0360
A 313-000000 2009002 20090202 AAA 112 702887.96 702887.96 D CC CT0860


*** Sent via Developersdex http://www.developersdex.com ***

Joel, logically, this is exactly what i need, but formula doesnt work,
gives me #VALUE...

This is your formula.... any ideas?

=SUMPRODUCT(--(OR(LEFT($B$2:$B$60,3)="312",LEFT($B$2:$B$60,3)="3 13")),--
($K$2:$K$60="*ct08*"),--($R$2:$R$60000="B60"),$H$2:$H$60)

*** Sent via Developersdex http://www.developersdex.com ***