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.

 

[nmounir]

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

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

Hello

I am trying to calculate the cost (in column D) of all corrosion products (Corrosion A, Corrosion B, Corrosion C and Corrosion D) in column C that were used in the 8 1/2" and 8 1/2" ST sections (Column B) in Well 1. This is an Or logical test where I cannot use SUMIFS. I tried Sumproduct in cell E3 and you can find the formulatext in E5 and i didn't get the correct result because it calculated the 8 1/2" only. I tried SUMPRODUCT in cell G3 where I used the search function with an asterisk and the result was wrong because it included the 8 1/2", 8 1/2" ST and APP 8 1/2". I am looking for 8 1/2" and 8 1/2" ST only.
I then figured to use SUMIFS multiple times and add the outputs. Please see result in Cell E4 (formulatext in E8). This one got the correct answer which is 389.
the correct criterias are the ones I marked in green whereas the ones in yellow are not to be included.
I found the answer using multiple SUMIFS just today. However, before i found this answer i was trying yesterday to solve it with sumproduct and the search function. Is there a way to solve it with Sumproduct?

 

[Fluff]

How about

=SUM(SUMIFS(D3:D35,A3:A35,"Well 1",B3:B35,H1&"*",C3:C35,{"Corrosion A","Corrosion B","Corrosion C"}))

 

[nmounir]

How about

=SUM(SUMIFS(D3:D35,A3:A35,"Well 1",B3:B35,H1&"*",C3:C35,{"Corrosion A","Corrosion B","Corrosion C"}))

Thank u fluff. This one solved the issue !

 

[Fluff]

Glad we could help & thanks for the feedback.