Sumproduct /Search function with Wildcard

[nmounir]

Hello Everyone,

I am trying to use Sumproduct along with the search function instead of SUMIFS to conduct an logical text. Part this of formula is finding the cost of the 8 1/2" section. (drilling phases for oil wells). I am looking to get the cost of 8 1/2" regardless if is 8 1/2" or 8 1/2" ST. it is easily doable with SUMIFS but i cannot do it with sumproduct and the search function using wildcards. Your help is greatly appreciated.

 

[Fluff]

Try

search(G4,D3:D8)

 

[nmounir]

Try

search(G4,D3:D8)

It worked but I think I didn't explain it well. I am unable to use wildcard with sumproduct. as you can see below, i am getting 150 with SUMIFS and 190 with sumproduct and the search functions. I am looking here for the cost of 8 1/2" followed by any character and not 8 1/2" only. SUMIFS is giving the correct answer but not Sumproduct doesn't

 

[Fluff]

There is no need to use the wildcard when using search.
The sumproduct formula does work, but it's simply not doing what you want. Just stick with the sumifs, it's far better.

 

[nmounir]

There is no need to use the wildcard when using search.
The sumproduct formula does work, but it's simply not doing what you want. Just stick with the sumifs, it's far better.

The reason why I have to use Sumproduct is because this is just part of the formula, the rest of he formula consists of a OR logical test so I have to use Sumproduct. SUMIFS doesn't do or logical test.

 

[Fluff]

In that case can you please post some sample data, along with what you are trying to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[nmounir]

In that case can you please post some sample data, along with what you are trying to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I added the XL2BB but I can't use it for some reason. the Excel mini sheet is grayed out also. how can i send it?

 

[shinigamilight]

H3:

=SUMPRODUCT(IFERROR(SEARCH(G3&"*",D3:D8),0)*E3:E8)

 

[FormR]

the Excel mini sheet is grayed out also

You could see if this helps..

XL2BB Icons greyed out

Hi Team, i am unable to copy the data throught Xl2bb as buttom got disable please see the screen shot can anyone hlep me this. Regards Sanjeev

www.mrexcel.com

am looking here for the cost of 8 1/2" followed by any character

You're still probably better explaining the full picture - but for your specific question - here's one option.

Book1
	D	E	F	G	H
3	8 1/2"	100		8 1/2"	150
4	8 1/2" ST	50		8 1/2"	150
5	ST 8 1/2" ST	40
6	12 1/4"	30
7	12 1/4" ST	80
8	12 1/4" APP	60
Sheet1

Cell Formulas
Range	Formula
H3	H3	=SUMIFS(E3:E8,D3:D8,G3&"*")
H4	H4	=SUMPRODUCT(E3:E8,--(LEFT(D3:D8,LEN(G4))=G4))

 

[RoryA]

If you only want to match the start, you could use:

=SUMPRODUCT(E3:E8,--(IFERROR(SEARCH(G3,$D$3:$D$8),0)=1))