# Sumifs HELP!!! :)

#### jzouy

##### New Member
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

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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?

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

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

Replies
3
Views
245
Replies
3
Views
81
Replies
0
Views
1K
Replies
6
Views
200
Replies
1
Views
348

1,196,344
Messages
6,014,727
Members
441,841
Latest member
Prabu_sanku

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back