Quickie Sumporduct and 2 wildcards in one column

[SCG]

Afternoon All,

It is Friday afternoon and my brain has finally given up. Anyone know how I can include two wildcards in the forumla.

=SUMPRODUCT(ISNUMBER(SEARCH("f",B2:B5,1))*(C2:C5="car allowance")*(D2:D5))

I want to look up F and add another wildcard say R for the same column.

=SUMPRODUCT(ISNUMBER(SEARCH("f" & "r",B2:B5,1))*(C2:C5="car allowance")*(D2:D5))

Is this possible?

Thanks all

 

[SCG]

Any thoughts?

 

[T. Valko]

Afternoon All,

It is Friday afternoon and my brain has finally given up. Anyone know how I can include two wildcards in the forumla.

=SUMPRODUCT(ISNUMBER(SEARCH("f",B2:B5,1))*(C2:C5="car allowance")*(D2:D5))

I want to look up F and add another wildcard say R for the same column.

=SUMPRODUCT(ISNUMBER(SEARCH("f" & "r",B2:B5,1))*(C2:C5="car allowance")*(D2:D5))

Is this possible?

Thanks all

Which condition applies:

1. if the cell contains F or R
2. if the cell contains F and R

 

[SCG]

Which condition applies:

1. if the cell contains F or R
2. if the cell contains F and R

Thanks the cells could contain F or R

 

[T. Valko]

Thanks the cells could contain F or R

Can a cell contain BOTH?

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F
1	f	r	Car allowance	2500	1000	1500
2		a	Car allowance	200
3		fr	Car allowance	350
4		abc f	Car allowance	500
5		abc rf	Car allowance	650
1b

Excel 2003

Cell Formulas
Range	Formula
D1	=SUMPRODUCT((C2:C5=C1)*(ISNUMBER(SEARCH(A1:B1,B2:B5)))*(D2:D5))
E1	=SUMPRODUCT(--(C2:C5=C1),--(ISNUMBER(SEARCH(A1,B2:B5))),--(ISNUMBER(SEARCH(B1,B2:B5))),(D2:D5))
F1	=D1-E1

 

[Aladin Akyurek]

Also...

Either with a constant array of search sitring

=SUMPRODUCT(
     --(MMULT(--ISNUMBER(SEARCH({"f","r"},$B$2:$B$7))+0,
          ROW($B$2:INDEX($B$2:$B$7,COLUMNS({"f","r"})))^0)>0),
     --($C$2:$C$7="car allowance"),
     $D$2:$D$7)

which needs just enter;

Or with a list of search strings in a vertical range of their own:

=SUMPRODUCT(
     --(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(F2:F3),$B$2:$B$7))+0,
          ROW(F2:F3)^0)>0),
     --($C$2:$C$7="car allowance"),
     $D$2:$D$7)

which requires control+shift+enter, not just enter.

 

[T. Valko]

Also...

Either with a constant array of search sitring

=SUMPRODUCT(
    --(MMULT(--ISNUMBER(SEARCH({"f","r"},$B$2:$B$7))+0,
         ROW($B$2:INDEX($B$2:$B$7,COLUMNS({"f","r"})))^0)>0),
    --($C$2:$C$7="car allowance"),
    $D$2:$D$7)

which needs just enter;

Or with a list of search strings in a vertical range of their own:

=SUMPRODUCT(
    --(MMULT(--ISNUMBER(SEARCH(TRANSPOSE(F2:F3),$B$2:$B$7))+0,
         ROW(F2:F3)^0)>0),
    --($C$2:$C$7="car allowance"),
    $D$2:$D$7)

which requires control+shift+enter, not just enter.

Why use +0 and "--" on the same expression?

Those can be greatly simplified.