help with an =IF formula

[BSWJON]

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

 

[BSWJON]

oh and in my example the number value attributed to the search criteria would be '1' hence why the example result would be 3.

 

[jasonb75]

oh and in my example the number value attributed to the search criteria would be '1' hence why the example result would be 3.

Not quite sure what you mean there, maybe I'm missing something in an earlier post?

Looking at your example, I'm seeing length and type specifications for big bits of chopped up tree. Are you saying now that you want to calculate the formula based on type, length, or possibly both?

Because your source table has both length and type in the same cell, excel sees the lengths as text, not as a number, there is no known way to mix both types in the same cell, you have to tweak the formula to split it down and deal with each part individually.

As an example, with 3 entered in D32, as a number

=SUMIF(D6:D28,"*"&TEXT(D32,"#.#"&"*",E6:E28)

Would find 3.0 in the text string but would ignore 3.3, 3.6, etc.

Do you need the formula to be mixed so that you can specify a type with length optional, or even a range of lengths?

Which version of excel are you using? If you do need the mixed formula, it would be a lot easier to do using sumifs.

 

[BSWJON]

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?

Jason, here is the post I put before the one you replied to- guessing you didn't see this bit as it's on a different page.

Essentially yes is the answer, I am wanting to search for text and numbers with the same formula. I am using data validation drop down boxes in cell D32, some of which show tree species (pine, spr, lar, etc) and some which show lengths (2.4,3.0,3.3,3.6,4.0,4.2, etc). I have chosen to use these drop down boxes so that unnecessary data is not shown/does not clutter the sheet. So the formula needs to search the array D6:D34 for whatever is in cell D32, and sum the attirbuted data in cells E6:E34.

The formula works brilliantly when searching for text but not so well when searching for numerical values which end in .0. When searching for numbers ending in anything else, it works fine. So the problem that needs resolving is getting the formula to search more specifically. Would your formula above work in this way?

I am using excel 2007.

The formula does not necessarily have to be mixed as I have separate cells for searching tree species (text) and lengths (numerical values).

 

[BSWJON]

quick update: the formula =SUMIF(D6:D28,"*"&TEXT(D32,"#.#"&"*",E6:E28) has too many arguements according to excel...

 

[JRidge]

Jason, here is the post I put before the one you replied to- guessing you didn't see this bit as it's on a different page.

Essentially yes is the answer, I am wanting to search for text and numbers with the same formula. I am using data validation drop down boxes in cell D32, some of which show tree species (pine, spr, lar, etc) and some which show lengths (2.4,3.0,3.3,3.6,4.0,4.2, etc). I have chosen to use these drop down boxes so that unnecessary data is not shown/does not clutter the sheet. So the formula needs to search the array D6:D34 for whatever is in cell D32, and sum the attirbuted data in cells E6:E34.

The formula works brilliantly when searching for text but not so well when searching for numerical values which end in .0. When searching for numbers ending in anything else, it works fine. So the problem that needs resolving is getting the formula to search more specifically. Would your formula above work in this way?

I am using excel 2007.

The formula does not necessarily have to be mixed as I have separate cells for searching tree species (text) and lengths (numerical values).

Hi,

Sorry i don,t see the problem.

With this formula:

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

I can search for Lar, Spr, Pine, 3.0, 4.0, 4.8 etc and all gives correct responce.

D32 needs to be formated as Text so it searches for exactly what you put in it. searchs 3.0 and not 3


Regards

Jeremy

 

[BSWJON]

There must be something I am missing here because it's just not working for me. it is also now including data attributed to the 'total' rows too! I wish I could post a screenshot of it to show you (I don't have the software add in or whatever it is you need to facilitate this)

 

[Tamarad]

You should share (part of) the file using sendspace.com?

 

[JRidge]

This is how i have it set up

https://www.sendspace.com/file/67w5lt

Right click D32 and select format cell, Format as text.

 

[BSWJON]

Thanks for that. Not working still though. Here is a sample of the sheet:

https://fs11u.sendspace.com/upload?...0.1405427857.3EAC6D94.25.0&DESTINATION_DIR=16

E42 is where the formula needs to be, D42 is where the search criteria are, D6:D34 is the search array.

 

[JRidge]

Thanks for that. Not working still though. Here is a sample of the sheet:

https://fs11u.sendspace.com/upload?...0.1405427857.3EAC6D94.25.0&DESTINATION_DIR=16

E42 is where the formula needs to be, D42 is where the search criteria are, D6:D34 is the search array.

I cant get your example for some reason, your link fails..

From what you say above,

D42 needs to be formatted as text and will have your drop down list in.

E42 will have this formula:

[COLOR=#333333]=Sumif(D6:D34,"*"&D42&"*",E6:E34)[/COLOR]

or try uploading your file again.

Regards

Jeremy