SOLVED

Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1958944%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958944%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20see%20if%20there's%20a%20way%20to%20make%20this%20happen%20automatically%20in%20excel.%3C%2FP%3E%3CP%3EIf%20I%20have%20a%20list%20several%20(including%20multiples)%20items%20and%20in%20another%20column%20I%20have%20the%20cost%20price%2C%20is%20there%20a%20way%20I%20could%20automatically%20calculate%20all%20of%20one%20item.%3CBR%20%2F%3Ee.g.%3C%2FP%3E%3CP%3EApples%26nbsp%3B%202p%3C%2FP%3E%3CP%3EBananas%2012p%3C%2FP%3E%3CP%3EApples%2013p%3C%2FP%3E%3CP%3EGrapefruits%203p%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20I%20could%20calculate%20this%20automatically%3F%3C%2FP%3E%3CP%3EIe%20a%20lookup%20or%20if%20Apples%2C%20then%20sum%20of%20the%20next%20column%3F%3CBR%20%2F%3E%3CBR%20%2F%3EHelp%20me%20please!!%20Many%20thanks%3C%2FP%3E%3CP%3ECal%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1958944%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1958966%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958966%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891939%22%20target%3D%22_blank%22%3E%40CalJones%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECal%2C%20you%20can%2C%20but%20the%20question%20is%20how%20actually%20the%20price%20is%20defined.%20Is%20%222p%22%20the%20text%2C%20or%20that's%20number%20formatted%20as%202p%2C%20and%20is%20that%20always%20%22p%22%20at%20the%20end%20of%20the%20price%20or%20that%20could%20be%20another%20notation.%20Better%20if%20you%20provide%20small%20sample%20file%2C%20with%20data%20as%20in%20your%20post%2C%20to%20illustrate%20formatting.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1958970%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958970%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20show%20my%20data%20as%20it%20contains%20sensitive%20info.%3C%2FP%3E%3CP%3EPlease%20find%20attached%20a%20mock%20layout%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1958971%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958971%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891939%22%20target%3D%22_blank%22%3E%40CalJones%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you.%20We%20don't%20need%20your%20entire%20file%2C%20just%20what%20is%20on%20screenshot%20but%20as%20file.%3C%2FP%3E%0A%3CP%3EIf%20I%20understood%20correctly%20you'd%20like%20to%20sum%20(OUT)%20by%20Item%2C%20perhaps%20by%20month%2Fyear%20as%20well.%20When%20PivotTable%20will%20be%20a%20right%20solution.%20As%20option%20SUMIFS(out%20column%2C%20item%20column%2C%20%22Apples%22).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1958972%22%20slang%3D%22de-DE%22%3ESubject%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958972%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F891939%22%20target%3D%22_blank%22%3E%40CalJones%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EHere%20is%20a%20small%20and%20simple%20example%20of%20how%20something%20like%20this%20could%20be%20done%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EIt%20works%20like%20a%20small%20calculator%2C%20you%20just%20have%20to%20select%20the%20product%20via%20the%20dropdown%20menu%20and%20the%20number%20of%20the%20product%20...%20Everything%20else%20is%20automatic.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EAs%20long%20as%20the%20correct%20individual%20price%20per%20product%20was%20set%20at%20the%20beginning.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello all,

I'm trying to see if there's a way to make this happen automatically in excel.

If I have a list several (including multiples) items and in another column I have the cost price, is there a way I could automatically calculate all of one item.
e.g.

Apples  2p

Bananas 12p

Apples 13p

Grapefruits 3p

Is there a way I could calculate this automatically?

Ie a lookup or if Apples, then sum of the next column?

Help me please!! Many thanks

Cal

9 Replies

@CalJones 

Cal, you can, but the question is how actually the price is defined. Is "2p" the text, or that's number formatted as 2p, and is that always "p" at the end of the price or that could be another notation. Better if you provide small sample file, with data as in your post, to illustrate formatting.

@Sergei Baklan 

I can't show my data as it contains sensitive info.

Please find attached a mock layout etc.

 

best response confirmed by CalJones (New Contributor)
Solution

@CalJones 

Thank you. We don't need your entire file, just what is on screenshot but as file.

If I understood correctly you'd like to sum (OUT) by Item, perhaps by month/year as well. When PivotTable will be a right solution. As option SUMIFS(out column, item column, "Apples").

@CalJones 

Here is a small and simple example of how something like this could be done.

It works like a small calculator, you just have to select the product via the dropdown menu and the number of the product ... everything else is automatic. As long as the correct individual price per product was set at the beginning.

 

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

@NikolinoDE 

This data structure is some different from the screenshot.

Couldn't see the previous answer to understand better. For me the whole thing always hangs a bit, somehow I get the updates in the forum always delayed ... strange. Anyway it is how it is, do you carry on?

@NikolinoDE 

Usually I refresh before answer, previous post doesn't appear automatically. Anyway, it's always better to have the sample, we may have a lot of guesses on what is actually required.

Thank you very much.
Cal

@CalJones , you are welcome