Array formula - searching 1st x characters of field value

[craigexcel]

I need to create a CSE formula that looks at, in part, only the 1st 5 characters of one of the fields. Specifically, one of the criteria is to find values in a field (column) that begin with "HOUSE". I'm not sure how to create the CSE formula to look at the 1st 5 characters to determine if a given record should be included in the sum or not. Is there a way to do this?

 

[Aladin Akyurek]

I need to create a CSE formula that looks at, in part, only the 1st 5 characters of one of the fields. Specifically, one of the criteria is to find values in a field (column) that begin with "HOUSE". I'm not sure how to create the CSE formula to look at the 1st 5 characters to determine if a given record should be included in the sum or not. Is there a way to do this?

Your problem description is incomplete in that the conditions are probably not fully spelled out...

Maybe one of...

=SUMIF(StringRange,X2&"*",SumRange)

where X2 houses a value like HOUSE.

Control+shift+enter, not just enter:

=SUM(IF(LEFT(CondRange1,5)=X2,IF(CondRange2=Y2,SumRange)))

where Y2 houses another condition to test for.

The latter can also be done with SUMIFS...

=SUMIFS(SumRange,CondRange1,X2&"*",CondRange2,Y2)

 

[T. Valko]

I need to create a CSE formula that looks at, in part, only the 1st 5 characters of one of the fields. Specifically, one of the criteria is to find values in a field (column) that begin with "HOUSE". I'm not sure how to create the CSE formula to look at the 1st 5 characters to determine if a given record should be included in the sum or not. Is there a way to do this?

Something like this...

Book1
	A	B	C	D
2	House Of Pain	43	_	181
3	Our House	21	_	_
4	Horse Lipps	66	_	_
5	House Party	53	_	_
6	Little Pink Houses	85	_	_
7	House Music	85	_	_
Sheet1

This formula entered in D2:

=SUMIF(A2:A7,"House*",B2:B7)

 

[craigexcel]

Thanks Aladin & Biff, for your suggestions. Agreed, it looks like I needed to have included a bit more information. Following is the CSE formula I currently have: =SUM((INV!$C$5:$C$50000=O18)*(INV!$I$5:$I$50000=$N$2)*(INV!$X$5:$X$50000="YES")*(INV!$S$5:$S$50000))

--- where, [O18] = "HOUSE"
--- Col I is a numeric month value
--- Col X contains YES or NO
--- Col S contains the $$ values to be summed

So, I want to sum those values in Col S, where:
Col O values begin with "HOUSE",
Col I values = 7 (for July), and
Col X values = YES

Hopefully that's more clear. Thanks.

 

[T. Valko]

Thanks Aladin & Biff, for your suggestions. Agreed, it looks like I needed to have included a bit more information. Following is the CSE formula I currently have: =SUM((INV!$C$5:$C$50000=O18)*(INV!$I$5:$I$50000=$N$2)*(INV!$X$5:$X$50000="YES")*(INV!$S$5:$S$50000))

--- where, [O18] = "HOUSE"
--- Col I is a numeric month value
--- Col X contains YES or NO
--- Col S contains the $$ values to be summed

So, I want to sum those values in Col S, where:
Col O values begin with "HOUSE",
Col I values = 7 (for July), and
Col X values = YES

Hopefully that's more clear. Thanks.

What version of Excel are you using?

 

[Aladin Akyurek]

Thanks Aladin & Biff, for your suggestions. Agreed, it looks like I needed to have included a bit more information. Following is the CSE formula I currently have: =SUM((INV!$C$5:$C$50000=O18)*(INV!$I$5:$I$50000=$N$2)*(INV!$X$5:$X$50000="YES")*(INV!$S$5:$S$50000))

--- where, [O18] = "HOUSE"
--- Col I is a numeric month value
--- Col X contains YES or NO
--- Col S contains the $$ values to be summed

So, I want to sum those values in Col S, where:
Col O values begin with "HOUSE",
Col I values = 7 (for July), and
Col X values = YES

Hopefully that's more clear. Thanks.

Control+shift+enter, not just enter:

=SUM(
  IF(LEFT(INV!$C$5:$C$50000,LEN(O18))=O18,
  IF(INV!$I$5:$I$50000=$N$2,
  IF(INV!$X$5:$X$50000="YES",
    INV!$S$5:$S$50000))))

You might want also to work out a SUMIFS version (see my first post).

 

[Rick Rothstein]

Ignore... Responded to wrong person

 

[craigexcel]

Thanks to all for your suggestions! Aladin's CSE formula worked perfectly in Excel 2007.

 

[Aladin Akyurek]

Thanks to all for your suggestions! Aladin's CSE formula worked perfectly in Excel 2007.

Great. Thanks for providing feedback.

 

[T. Valko]

Thanks to all for your suggestions!

You're welcome!

Aladin's CSE formula worked perfectly in Excel 2007.

It looks like you can use the more efficient SUMIFS function if you're using Excel 2007.