Excel function needed

%3CLINGO-SUB%20id%3D%22lingo-sub-2380788%22%20slang%3D%22en-US%22%3EExcel%20function%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380788%22%20slang%3D%22en-US%22%3E%3CP%3EExcel%20novice%20here...%20I%20am%20in%20need%20of%20a%20function%20to%20give%20me%20the%20following%20outcome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20value%20in%20column%20A%20is%20the%20same%20then%20add%20the%20values%20in%20column%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20help%20is%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJoni%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2380788%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-2380884%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20function%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380884%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061734%22%20target%3D%22_blank%22%3E%40Joni_B%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%20%3CEM%3EIf%20the%20value%20in%20column%20A%20is%20the%20same...%3C%2FEM%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CSTRONG%3ESame%20as%20%3CFONT%20color%3D%22%23FF0000%22%3Ewhat%3C%2FFONT%3E%3F%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CEM%3E...then%20add%20the%20values%20in%20column%20B.%3C%2FEM%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CU%3E%20Value%20(%3CSTRONG%3Esingular%3C%2FSTRONG%3E)%3C%2FU%3E%20in%20Column%20A%2C%20%3CU%3Evalues%20(%3CSTRONG%3Eplural%3C%2FSTRONG%3E)%3C%2FU%3E%20in%20Column%20B%3A%20is%20that%20really%20what%20you%20mean%3F%20If%20so%2C%20please%20give%20example%20of%20what%20that%20might%20look%20like%2C%20along%20with%20what%20some%20other%20situations%20where%20the%20conditions%20might%20not%20be%20met.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20big--major--part%20of%20creating%20a%20functional%20spreadsheet%20is%20real%20clarity%20in%20expressing%20in%20words%20what%20it%20is%20that%20is%20to%20be%20done.%20Computers%20are%20very%20precise%2C%20and%20they'll%20do%20precisely%20what%20you%20instruct%20them%20to%20do%2C%20even%20if%20it%20turns%20out%20not%20to%20be%20what%20you%20meant%20them%20to%20do.%20That's%20why%20occasionally%20we%20read%20stories%20in%20the%20news%20of%20major%20financial%20institutions%20making%20major%20blunders%20based%20on%20spreadsheet%20projections%20of%20this%20that%20or%20the%20other.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHence%20my%20suggestion%20that%20you%20need%20to%20define%20what%20you%20need%20a%20bit%20more%20clearly.%20Once%20you've%20done%20that%2C%20we%20can%20happily%20and%20confidently%20offer%20some%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2380905%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20function%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380905%22%20slang%3D%22en-US%22%3EI%20have%20just%20uploaded%20a%20spreadsheet.%20The%20function%20I%20need%20is.%3CBR%20%2F%3E%3CBR%20%2F%3EOn%20the%20%22reg%20invoices%22%20tab.%20If%20the%20value%20in%20column%20D%20is%20the%20same%20then%20add%20the%20value%20in%20column%20I.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20values%20in%20column%20D%20are%20orders%20numbers.%20I%20need%20the%20total%20value%20associated%20with%20each%20order%20number.%3CBR%20%2F%3EI%20hope%20this%20makes%20more%20sense.%3CBR%20%2F%3ETHANK%20YOU!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2380967%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20function%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380967%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061734%22%20target%3D%22_blank%22%3E%40Joni_B%3C%2FA%3E%26nbsp%3BWhat%20you%20ask%26nbsp%3B%3CSPAN%3Efor%20is%20possible%20with%20a%20Pivot%20Table%2C%20I%20believe.%20Added%20a%20worksheet%20%22Sheet1%22%20with%20such%20a%20table.%20Is%20that%20what%20you%20hand%20in%20mind%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EMore%20about%20pivot%20tables%20in%20the%20attached%20link.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcreate-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576%23OfficeVersion%3DWindows%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fcreate-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576%23OfficeVersion%3DWindows%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2380983%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20function%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380983%22%20slang%3D%22en-US%22%3EYes!%20This%20is%20what%20I%20need!%20I%20have%20never%20used%20pivot%20tables.%20Will%20the%20link%20you%20provided%20be%20able%20to%20walk%20me%20through%20this%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2380984%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20function%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380984%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061734%22%20target%3D%22_blank%22%3E%40Joni_B%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20does%20make%20more%20sense...and%20I've%20created%20a%20solution.%20It%20may%20or%20may%20not%20be%20the%20best%20solution.%20It%20also%20depends%20on%20one%20relatively%20new%20function...UNIQUE...which%20requires%20the%20most%20recent%20versions%20of%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20I%20created%20a%20new%20sheet%20in%20your%20workbook%2C%20called%20%22Dashboard%2C%22%20and%20entered%20two%20formulas.%20The%20first%20is%20this%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DUNIQUE('reg%20invoices'!D1%3AD1926)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThat%20function%20is%20in%20Cell%20C3%20of%20the%20Dashboard%3B%20this%20function%20generates%20a%20complete%20list%20of%20all%20the%20unique%20invoice%20numbers%2C%20whether%20they%20appear%20just%20once%20or%20appear%20100s%20of%20times%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20in%20the%20adjacent%20column%2C%20I%20entered%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMIF('reg%20invoices'!%24D%241%3A%24D%241926%2CDashboard!C3%2C'reg%20invoices'!%24I%241%3A%24I%241926)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20copied%20it%20down%20to%20all%20the%20rows%20that%20had%20an%20invoice%20number%20in%20them.%20This%20gets%20the%20total%20associated%20with%20each%20invoice%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20see%20that%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%20has%20just%20given%20you%20another%20solution%2C%20equally%20functional%20(and%2C%20importantly%2C%20not%20relying%20on%20the%20new%20function%20UNIQUE.%20The%20Pivot%20Table%20solution%20is%20a%20good%20one%20as%20well.%26nbsp%3B%20One%20of%20the%20great%20aspects%20of%20Excel--which%20you're%20experiencing%20here--is%20that%20there%20are%20often%20two%20or%20more%20ways%2C%20quite%20distinctive%2C%20to%20accomplish%20the%20same%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2380876%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20function%20needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2380876%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061734%22%20target%3D%22_blank%22%3E%40Joni_B%3C%2FA%3E%26nbsp%3BYou%20write%20%22%3CSPAN%3EIf%20the%20value%20in%20column%20A%20is%20the%20same....%22.%20The%20same%20as%20what%2C%20I%20wonder.%20If%20you%20have%20a%20workbook%20that%20shows%20what%20you%20are%20trying%20to%20do%2C%20you%20can%20perhaps%20upload%20it.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Excel novice here... I am in need of a function to give me the following outcome.

 

If the value in column A is the same then add the values in column B.

 

All help is appreciated!

 

Thank you,

 

Joni 

7 Replies

@Joni_B You write "If the value in column A is the same....". The same as what, I wonder. If you have a workbook that shows what you are trying to do, you can perhaps upload it. 

@Joni_B 

 

You wrote: If the value in column A is the same...    Same as what??

...then add the values in column B.    Value (singular) in Column A, values (plural) in Column B: is that really what you mean? If so, please give example of what that might look like, along with what some other situations where the conditions might not be met.

 

A big--major--part of creating a functional spreadsheet is real clarity in expressing in words what it is that is to be done. Computers are very precise, and they'll do precisely what you instruct them to do, even if it turns out not to be what you meant them to do. That's why occasionally we read stories in the news of major financial institutions making major blunders based on spreadsheet projections of this that or the other.

 

Hence my suggestion that you need to define what you need a bit more clearly. Once you've done that, we can happily and confidently offer some help.

I have just uploaded a spreadsheet. The function I need is.

On the "reg invoices" tab. If the value in column D is the same then add the value in column I.

The values in column D are orders numbers. I need the total value associated with each order number.
I hope this makes more sense.
THANK YOU!

@Joni_B What you ask for is possible with a Pivot Table, I believe. Added a worksheet "Sheet1" with such a table. Is that what you hand in mind?

More about pivot tables in the attached link.

https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bf... 

Yes! This is what I need! I have never used pivot tables. Will the link you provided be able to walk me through this?

@Joni_B 

It does make more sense...and I've created a solution. It may or may not be the best solution. It also depends on one relatively new function...UNIQUE...which requires the most recent versions of Excel.

 

Basically I created a new sheet in your workbook, called "Dashboard," and entered two formulas. The first is this:

=UNIQUE('reg invoices'!D1:D1926)

That function is in Cell C3 of the Dashboard; this function generates a complete list of all the unique invoice numbers, whether they appear just once or appear 100s of times

 

Then, in the adjacent column, I entered:

=SUMIF('reg invoices'!$D$1:$D$1926,Dashboard!C3,'reg invoices'!$I$1:$I$1926)

and copied it down to all the rows that had an invoice number in them. This gets the total associated with each invoice number.

 

I see that @Riny_van_Eekelen has just given you another solution, equally functional (and, importantly, not relying on the new function UNIQUE. The Pivot Table solution is a good one as well.  One of the great aspects of Excel--which you're experiencing here--is that there are often two or more ways, quite distinctive, to accomplish the same result.

 

@Joni_B The link has it all. And there are many other resources on-line with videos tutorials etc. Just search for "pivot table excel".