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 ***