Prev: Using the FIND function in a loop
Next: trouble using checkbox26_DblClick
From: B Rabbit B on 4 May 2010 13:05
I have a spreadsheet and I am running a SUMIF to a second and I am having
trouble with the search criteria. The original reference is a 3 or 4 digit
number and the criteria I am searching for has a text discriptor in front of
that 3 or 4 digit number. Is there a way to have the SumIf formula figure out
that I am just looking for the number and not the text and number. The
criteria search does have a "-" between the word and the number.

i.e. I am searching for "134"and the column is set up "abc-1234" and I want
to use the 1234.

My formula is : =SUMIF('Labor March'!A6:A896,A9,'Labor March'!L6:L896)



From: Bob Umlas, Excel MVP on 4 May 2010 13:38
Try using SUMPRODUCT - something like
=SUMPRODUCT(N(MID('Labor March'!A6:A896,FIND("-",'Labor
March'!A6:A896)+1,10)=A9&""),'Labor March'!L6:L896)

Bob Umlas
Excel MVP

"B Rabbit" wrote:

> I have a spreadsheet and I am running a SUMIF to a second and I am having
> trouble with the search criteria. The original reference is a 3 or 4 digit
> number and the criteria I am searching for has a text discriptor in front of
> that 3 or 4 digit number. Is there a way to have the SumIf formula figure out
> that I am just looking for the number and not the text and number. The
> criteria search does have a "-" between the word and the number.
>
> i.e. I am searching for "134"and the column is set up "abc-1234" and I want
> to use the 1234.
>
> My formula is : =SUMIF('Labor March'!A6:A896,A9,'Labor March'!L6:L896)
>
>
>
 | 
Pages: 1
Prev: Using the FIND function in a loop
Next: trouble using checkbox26_DblClick