help with an =IF formula

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
107
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I have a formula that calculates totals based on specific text entered in another cell, which works fine:

=SUMIF(D6:D28,"=*pine*",E6:E28)

This formula is entered into cell E32. What I would like is for it to look at the contents of D32, and alter its' search criteria accordingly.

Something like,

=IF(D32,"*pine*")=SUMIF(D6:D28,"=*pine*",E6:E28)=IF(D32,"*Larch*")=SUMIF(D6:D28,"=*lar*",E6:E28)

etc etc.

I feel like I'm close to getting this right but I don't have enough knowledge to be able crack it.

Any help would be much appreciated.

Cheers

Jon
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi,

Not sure if this is exactly what your after but Paste this in E32.

Code:
=SUMIF(D6:D28,D32)

Change the Search Range D6:D28 to what ever is required.

Regards

Jeremy
 
Last edited:
Upvote 0
Hi everyone,

I have a formula that calculates totals based on specific text entered in another cell, which works fine:

=SUMIF(D6:D28,"=*pine*",E6:E28)

This formula is entered into cell E32. What I would like is for it to look at the contents of D32, and alter its' search criteria accordingly.

Something like,

=IF(D32,"*pine*")=SUMIF(D6:D28,"=*pine*",E6:E28)=IF(D32,"*Larch*")=SUMIF(D6:D28,"=*lar*",E6:E28)

etc etc.

I feel like I'm close to getting this right but I don't have enough knowledge to be able crack it.

Any help would be much appreciated.

Cheers

Jon

Can you give sample data of d2:E32. It will be easier to understand your question and provide answer
 
Upvote 0
Hi Jeremy,

Not sure that your answer helps unfortunately.

Here is data from D3 down to D32:

Month:Aug
BudgetedPlanned
4.0 Spr450450
4.8 Spr450450
4.8 Spr450450
4.8 Spr450450
3.6 Lar450450
Total22502250
4.8 Spr450450
4.8 Spr450450
4.8 Spr450450
4.8 Pine450450
4.8 Spr450450
Total22502250
2.4 Spr450450
3.0 Spr450450
3.6 Spr450450
4.0 Spr450450
4.2 Spr450450
Total22502250
2.8 Spr450450
3.3 Spr450450
3.3 Pine450450
2.8 DF450450
3.3 NF450450
Total22502250
Month Tot. 90009000
Pine900900

<colgroup><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Paste this in E32

Code:
=Countif(D6:D28,"*"&D32&"*",E6:E28)

and this in F32

Code:
=Countif(D6:D28,"*"&D32&"*",F6:F28)

Just be carefull with the search phrase in D32, Better to use Lar, Spr or Pine etc.
 
Last edited:
Upvote 0
Should be

Paste this in E32


Code:

Code:
=SUMIF(D6:D28,"*"&D32&"*",E6:E28)

and this in F32

Code:

Code:
=SUMIF(D6:D28,"*"&D32&"*",F6:F28)

Just be carefull with the search phrase in D32, Better to use Lar, Spr or Pine etc.
 
Upvote 0
That's perfect and exactly what I needed thank you.

Should be

Paste this in E32


Code:

Code:
=SUMIF(D6:D28,"*"&D32&"*",E6:E28)

and this in F32

Code:

Code:
=SUMIF(D6:D28,"*"&D32&"*",F6:F28)

Just be carefull with the search phrase in D32, Better to use Lar, Spr or Pine etc.
 
Upvote 0
Just be carefull with the search phrase in D32, Better to use Lar, Spr or Pine etc.

Not strictly necessary, something like

=SUMIF(D6:D28,"*"&LEFT(D32,3)&"*",E6:E28)

Will work with the original keywords in D32.
 
Upvote 0
Thanks again Jeremy for your help and your input Jason.

I have a slight snag- when applying this formula to cells where the formula must search for numbers instead of text, it ignores numbers after the decimal point and includes them all- i.e if I have values within the array that the formula searches in where are similar to one decimal point, it will add them together. so if in the array I have the numbers 3.0, 3.3, 3.6, the formula, when selecting to search for say, 3.0, will return the value '3' as there are also 3.3 and 3.6 present. This only occurs when the search criteria ends in .0, i.e if searching for 3.6 it will return the correct value.

So my question now is, is there a modification to the formula or perhaps cell formatting which can be made which makes it specific enough to search for exactly 3.0 or 3.6 (etc), however is still able to identify the numbers within a cell when there is text present also?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top