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]

I really appreciate you taking the tim to help me with this thank you. Unfortunately the formula didn't work again. Here is another link though:

https://www.sendspace.com/file/9myldm

 

[JRidge]

Here you go..

https://www.sendspace.com/file/7bexhm

You had 4,0 in search criteria and in table it was 4.0

Works now

This one is with all search boxes correct:

https://www.sendspace.com/file/0muaa0

Regards

Jeremy

 

[BSWJON]

Still not working when I copy it into my document.

Here is the full document, perhaps there is something else I am missing?!

https://www.sendspace.com/file/61gy7p

 

[JRidge]

This one has all search boxes set up, if required:

https://www.sendspace.com/file/0muaa0

 

[JRidge]

Your last upload works fine for me, use the dropdown in D42 to select 4.0

I do not know why it is not working for you. Are your numbers all with , or .

 

[JRidge]

Here is your full Document, every in the first month works now.

https://www.sendspace.com/file/vs1cy8

On your system confirm that which ever decimal marker you are using in the Table , or . is the same as in your search drop down box.

Regards

jeremy

 

[BSWJON]

Hi I think I must have been a bit hasty with saying that it wasn't working, it now seems to be working.

 

[jasonb75]

Looks like a lot has been discussed since I last looked at this thread, rather than trying to follow the conversation and find what has and hasn't been covered, I'm just going to pick a couple of points that I think are relevant to the issue of lengths such as 3.0 not returning the correct result.

Regardless of how a number is passed into countif / sumif, it is converted back to numeric format and truncated to remove any trailing zeros. This means that no matter how you try it, 3.0 will always return 3 in the search criteria, as a character, not a value, so 3.0, 3.3, 3.6, 6.3 are all valid matches.

The formula that I tried to post before had a couple of errors that I failed to notice, the correct version is

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

Which should force the format to give the correct result by length.

 

[vnoreau]

Hello everyone,

I am having an issue with embedded IF functions.

Here is the break down of the cells and formulas. The explanation of what I need follows the breakdown.




H11 - Currency
Drop down menu with various choices ex: CAD - QC

I11 - Expense Total Including Taxes
User enters the total amount of purchase

J11 - GL Code (Auto populated based on description selected)
=IF(ISBLANK(C11),"",(VLOOKUP(C11,Description,2,FALSE)))

K11 - Total Before Taxes (Amount calculated once the taxes are calculated and this is where the circular reference occurs)
=IF(ISBLANK(I11),"",(I11-M11))

L11 - Tax 1 - tax calculation based on results from the vlookup and amount before tax
=IF(ISBLANK(I11),"",(J11*O11))

M11 - Tax 2 - tax calculation based on results from the vlookup and amount before tax
=IF(ISBLANK(I11),"",(J11*P11))

N11 - Total Taxes
=IF(ISBLANK(I11),"",(K11+L11))

O11 - Total Paid (This is where the issue lies....or part of it)
=IF(G11>0,G11*$H$8,)+I11

P11 - % Tax 1 - vlookup to get the Tax % based on the location of purchase
=IF(ISBLANK(I11),"",(VLOOKUP(H11,Taxes,3,FALSE)))

Q11 - % Tax 2 - vlookup to get the Tax % based on the location of purchase
=IF(ISBLANK(I11),"",(VLOOKUP(H11,Taxes,4,FALSE)))





What I need is that if KM is selected as a description, then multiply the total in cell I11 by H8, if any other description is selected then the tax calculation starts based on the Currency that was selected in cell H11. If OTHER is selected in cell H11, then the total in cell I11 is multiplied by Cell C7. (C7 is where the user enters the currency rate of the day).


Does this make enough sense to understand?

Thank you
Vanessa