Sumifs HELP!!! :)

[jzouy]

hey guys,

Im a complete rookie here so im hoping that all you gurus out there can give me some help with the project that im working on.

so basically I have a table that look like so:

Depth	Well Rate	Pressure	WGR	FBHP
50	0.75	35	50	215.775
50	1	35	50	214.048
100	0.75	35	50	226.834
100	0.75	55	100	78.0548
150	1	85	100	115.193

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

what im trying to do is to write a function/ macro that will tell me what the FBHP value is based on a certain Depth, Well Rate, Pressure and WGR.

I have wrote a Sumifs statement that tells me the FBHP value if the Depth, Well Rate, Pressure & WGR value is exactly the same as the number in the table.
i.e if depth is 50, well rate is 0.75 , pressure is 35 and WGR is 50. It tells me that the FBHP is 215.775

However, i was wondering is there a way which if for example, the Depth is 80, well rate is 0.9, pressure is 38 & WGR is 80. It will sort of look up to the closest line.
I.e for Depth is 80 it will go and look for the 100 value since its closest.

I hope my question makes sense.

Thanks soo much for taking a look..

please let me know if theres anything i could do..

Cheers,
Jay

 

[cyrilbrd]

Given in A1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { vertical-align: middle; white-space: normal; }.xl66 { text-align: right; vertical-align: middle; white-space: normal; }</style>

Depth	Well Rate	Pressure	WGR	FBHP
50	0.75	35	50	215.775
50	1	35	50	214.048
100	0.75	35	50	226.834
100	0.75	55	100	78.0548
150	1	85	100	115.193

<colgroup><col style="width:65pt" span="5" width="65"> </colgroup><tbody>
</tbody>

In A10:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>

depth	100
well rate	0.75
pressure	55
wgr	100
fbhp	78.0548

<colgroup><col style="width:65pt" span="2" width="65"> </colgroup><tbody>
</tbody>

Formula in B14 is =IFERROR(INDEX(E2:E6,MATCH(1,INDEX((A2:A6=B10)*(B2:B6=B11)*(C2:C6=B12)*(D2:D6=B13),0),0)),"no match")

Would that work for you?

 

[jzouy]

Given in A1:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { vertical-align: middle; white-space: normal; }.xl66 { text-align: right; vertical-align: middle; white-space: normal; }</style>

Depth	Well Rate	Pressure	WGR	FBHP
50	0.75	35	50	215.775
50	1	35	50	214.048
100	0.75	35	50	226.834
100	0.75	55	100	78.0548
150	1	85	100	115.193

<tbody>
</tbody>

In A10:
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>

depth	100
well rate	0.75
pressure	55
wgr	100
fbhp	78.0548

<tbody>
</tbody>

Formula in B14 is =IFERROR(INDEX(E2:E6,MATCH(1,INDEX((A2:A6=B10)*(B2:B6=B11)*(C2:C6=B12)*(D2:D6=B13),0),0)),"no match")

Would that work for you?

Hi mate,

thanks very much for the help!!

i was able to get that working w/ a sumifs statement. However, the problem that I have at the moment is say that I have a depth of 80 m, normally it will say 'false' or 'no match'. I was hoping that theres a way to tell excel to display the FBHP value for depth of 100 b/c 80 is closest to 100..

does that make sense?

thanks mate..

 

[cyrilbrd]

try =IFERROR(INDEX(E2:E6,MATCH(1,INDEX((A2:A6>=B10)*(B2:B6=B11)*(C2:C6=B12)*(D2:D6=B13),0),0)),"no match")