Wrong SUM value on a profit calc.

[Luther.King]

Somehow this formula it's not working and i can't figure out why.


=B4-I4-J4-(SUMIFS([Inventory2014.xlsx]Inventory!F:F;[Inventory2014.xlsx]Inventory!A:A;$A4))


B4 it's 10€
I4 it's 0,71€
J4 it's 0,44€

and A4 on SUMIF it's 2,39€.

Shows me a result 8,85€, somehow it's not subtracting A4.

Am i doing something wrong here?

[FDibbins]

Note that the sumifS() function needs the sum-range as the 1st argument, it loks like column A is that range?

The sumif() rakes the criteria range argument 1st, so either swap to sumif() or change the ranges in sumifS() - you only really need to use sumifS() if you have more than 1 criteria...
sum the income from all red shoes sold to women on sunday's

[shg]

A4 is on the same sheet that B4, I4, and J4 are, not Inventory. Maybe you want

=B4-I4-J4-SUMIFS([Inventory2014.xlsx]Inventory!F:F; [Inventory2014.xlsx]Inventory!A:A; [Inventory2014.xlsx]Inventory!$A4)

[Richard Buttrey]

Hi,

We really need to see the workbook to comment. But does column F in the Inventory2014 workbook on the Inventory sheet total 2.39 where column A on that same workbook is the same as A4 on your CURRENT workbbok? Did you perhaps mean that to be

Please Login or Register  to view this content.

[Luther.King]

First of all, thank you guys for help, really appreciate it!

I tried both formulas posted above and the result it's still the same so i'll post the workbooks here.




Sales2014.xlsxInventory2014.xlsx

[FDibbins]

You have a trailing space atthe end of R01 in Sales April sheet.

It also doesnt like the "" in I and J...it doesnt like a negative "", maybe change that to 0 instead of ""?

[Luther.King]

You have a trailing space atthe end of R01 in Sales April sheet.

It also doesnt like the "" in I and J...it doesnt like a negative "", maybe change that to 0 instead of ""?

I need that trailing space because i'll import a sales file from the website with the item code before item designation. It will come like this: "R01 Brand New Bike..."


I changed to 0 and cells now show "- €"

[FDibbins]

If you need it on 1 side, add it on the other side as well. As far as excel is concerned, "RO1" is not the same as "RO1 "
Perhaps change the last part to...
LEFT($A4,3) instead of just $A4

[Luther.King]

I did those changes but now i have "- €" on I4 and J4.

[FDibbins]

I am out of time right now, I should be able to take another look at this in a few hours

[Luther.King]

Ok, thank you.

[Luther.King]

Maybe someone else can help me please!

[Richard Buttrey]

Hi,

1. Remove the trailing space from A4 on inventory
2.As a consequence of 1, simplify I4

Please Login or Register  to view this content.

changing the final "", to a zero
3. Change the final "" in J4 to a zero too
4. K4:

Please Login or Register  to view this content.

[Luther.King]

Hi Richard.

I need that trailer space to count stock sold because i import a sales csv from the website and Sales!A4 comes like this: "R01 Mountain Bike"

Always code first then space and then item description.


Is there some other way to fix this without remove that trailer?

[Luther.King]

Can i use(and how) the same trailing space on K4?

=B4-I4-J4-(SUMIFS([Inventory2014.xlsx]Inventory!F:F;[Inventory2014.xlsx]Inventory!A:A;$A4))

[Richard Buttrey]

Hi,

It works for me if you use the TRIM() function in the formula. i.e.

Please Login or Register  to view this content.

As a general point when importing data you can define the split points in text fields at the time of import so that you don't have redundant spaces in data. That's a tidier solution than having to work around with a TRIM() or any other functions.

[FDibbins]

OK how about something like this?
=IF(B5="","",B5-I5-J5-(SUMIFS([Inventory2014.xlsx]Inventory!F:F,[Inventory2014.xlsx]Inventory!A:A,$A5)))

This is based on, if B is empty, I and J will also be empty, so you wont need a value in K

edit: this may work better...
=IF(B5="","",B5-IF(I5="",0,I5)-IF(J5="",0,J5)-(SUMIFS([Inventory2014.xlsx]Inventory!F:F,[Inventory2014.xlsx]Inventory!A:A,$A5)))

Also, you dont need to ARRAY enter formulas liks...
={SUMIF(Orders!C:C,A7,Orders!D:D)-(H7)}, you just need...
=SUMIF(Orders!C:C,A7,Orders!D:D)-H7

[Luther.King]

Hi,

It works for me if you use the TRIM() function in the formula. i.e.

Please Login or Register  to view this content.

As a general point when importing data you can define the split points in text fields at the time of import so that you don't have redundant spaces in data. That's a tidier solution than having to work around with a TRIM() or any other functions.

Richard you are like God!

You saved my *** here :D

Thank you soooo much!

[Luther.King]

OK how about something like this?
=IF(B5="","",B5-I5-J5-(SUMIFS([Inventory2014.xlsx]Inventory!F:F,[Inventory2014.xlsx]Inventory!A:A,$A5)))

This is based on, if B is empty, I and J will also be empty, so you wont need a value in K

edit: this may work better...
=IF(B5="","",B5-IF(I5="",0,I5)-IF(J5="",0,J5)-(SUMIFS([Inventory2014.xlsx]Inventory!F:F,[Inventory2014.xlsx]Inventory!A:A,$A5)))

Also, you dont need to ARRAY enter formulas liks...
={SUMIF(Orders!C:C,A7,Orders!D:D)-(H7)}, you just need...
=SUMIF(Orders!C:C,A7,Orders!D:D)-H7

Thank you for help but i solved it with Richard trim function.

[FDibbins]

Im just glad you got it resolved

Did you read my last comment in post # 18?
Thanks for the feedback

[Luther.King]

Unfortunately Richard's tip didn't work as well :'(

FDibbins i tried your formulas and they are also with the same problem: works when there's just the code and space (R01 ) but doesn't work when i have code, space, item description(R01 mountain bike) and i reeeeeally need it to work with code, space, item description.

Please take a look at this workbooks to see what i'm talking about:



Sales2014.xlsxInventory2014.xlsx

[FDibbins]

I cannot see what you need the trailing space. If you are extracting the RO1 from something for use elsewhere, then extracting it without the training space will give you pretty much the same as extracting it with the trailing space. Keep your data in teh same format and the problem will go away - either have the the trailing space both side, or dont haqve it either side

[Richard Buttrey]

Unfortunately Richard's tip didn't work as well :'(


Attachment 309661Attachment 309662

The reason is you've introduced another variable in the form of the Item Ref R01 now having a description attached. viz. R01 mountain bike. When you are comparing values in one workbook with another you need to compare EXACTLY the same values.

You'll need to change the formula in K4 on Saleas 2014 April sheet to

Please Login or Register  to view this content.

[Luther.King]

Hi.

Works but not very well.

It's assuming R01 very well but when i enter the item code R011 or R012 or R012011 he also assumes the values of R01 and doesn't calculates R012 values.

Please take a look at the workbook with those examples:

Inventory2014.xlsxSales2014.xlsx

[Luther.King]

Ok, no finally worked!!!!

=B4-I4-J4-(SUMIFS([Inventory2014.xlsx]Inventory!G:G;[Inventory2014.xlsx]Inventory!A:A;(LEFT(A4;FIND(" ";A4)-1))))



Thank's for help anyway.