Home

ARRAY??? IDENTIFYING MULTIPLE CONDITIONS, DIVIDING ONE CONDITION AND THEN ADDING???

%3CLINGO-SUB%20id%3D%22lingo-sub-832042%22%20slang%3D%22en-US%22%3EARRAY%3F%3F%3F%20IDENTIFYING%20MULTIPLE%20CONDITIONS%2C%20DIVIDING%20ONE%20CONDITION%20AND%20THEN%20ADDING%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-832042%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3EI%20want%20find%20one%20condition%20sum%20it%20and%20divide%20by%202%3C%2FP%3E%3CP%3EFind%20all%20NON-QUALIFIED%20with%2050%25%20Ownership%2C%20then%20add%20up%20those%20account%20balances%20and%20then%20divide%20by%202%3C%2FP%3E%3CP%3EFind%20all%20NON-QUALIFIED%20with%20100%25%20Ownership%20and%20add%20those%20account%20balances.%3C%2FP%3E%3CP%3EThen%2C%20add%20those%20two%20results%20together%20and%20with%20the%20final%20result%20appearing%20in%20one%20cell.%3C%2FP%3E%3CP%3EThanks%20in%20advance%20to%20anyone%20that%20can%20solve%20this...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-832042%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-832082%22%20slang%3D%22en-US%22%3ERe%3A%20ARRAY%3F%3F%3F%20IDENTIFYING%20MULTIPLE%20CONDITIONS%2C%20DIVIDING%20ONE%20CONDITION%20AND%20THEN%20ADDING%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-832082%22%20slang%3D%22en-US%22%3EP.S.%20The%20answer%20should%20be%20%241%2C272%2C355.07%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-832167%22%20slang%3D%22en-US%22%3ERe%3A%20ARRAY%3F%3F%3F%20IDENTIFYING%20MULTIPLE%20CONDITIONS%2C%20DIVIDING%20ONE%20CONDITION%20AND%20THEN%20ADDING%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-832167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401325%22%20target%3D%22_blank%22%3E%40pfmartens%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20a%20different%20result.%3C%2FP%3E%3CP%3ESee%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-832247%22%20slang%3D%22en-US%22%3ERe%3A%20ARRAY%3F%3F%3F%20IDENTIFYING%20MULTIPLE%20CONDITIONS%2C%20DIVIDING%20ONE%20CONDITION%20AND%20THEN%20ADDING%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-832247%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401325%22%20target%3D%22_blank%22%3E%40pfmartens%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20have%20a%20problem%20with%20your%20setup%20because%20you%20are%20merging%20cells%20ABC%2C%20DEF%2C%20GHIJ...%26nbsp%3B%3C%2FP%3E%3CP%3Ethat's%20not%20the%20best%20setup%20and%20it%20negatively%20impacts%20your%20calculations%3C%2FP%3E%3CP%3EWe%20need%20to%20fix%20this%20issue%20first%20and%20then%20create%20the%20function%3C%2FP%3E%3CP%3ESo%20I%20made%20some%20very%20basic%20changes%20in%20your%20setup%20by%20UNMERGING%20all%20cells.%20BTW%2C%20it%20requires%20more%20changes.%3C%2FP%3E%3CP%3ESee%20attached%20file%3C%2FP%3E%3CP%3Eand%20I%20copied%20the%20Criteria%20to%20columns%20E%20%26amp%3B%20F%3C%2FP%3E%3CP%3EI%20created%20a%202%20SumIfs%20functions%20in%20a%20random%20cell%20E6%3C%2FP%3E%3CP%3EThe%20Function%20is%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%3CFONT%20color%3D%22%233366FF%22%3ESUMIFS(C2%3AC10%2CA2%3AA10%2CE2%2CB2%3AB10%2CF3)%2F2%3C%2FFONT%3E%2B%3CFONT%20color%3D%22%23FF0000%22%3ESUMIFS(C2%3AC10%2CA2%3AA10%2CE2%2CB2%3AB10%2CF2)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EThe%20result%20should%20be%3A%26nbsp%3B%20%24%201%2C271%2C675.55%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F129525iEC22873F4FA2B7A9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Unmerge.png%22%20title%3D%22Unmerge.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20the%20attached%20file%3C%2FP%3E%3CP%3EAlso%20avoid%20Merge%20and%20Center%20command%20it%20has%20lots%20of%20problems.%20Watch%20this%20video%20about%20the%20alternate%20command%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DJHBNp8YAT0w%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DJHBNp8YAT0w%3C%2FA%3E%3C%2FP%3E%3CP%3ERemember%20also%20that%20ALIGNMENT%20is%20functional%20in%20EXCEL%20(Unlike%20Word%20%26amp%3B%20PowerPoint)%20so%20make%20sure%20you%20keep%20numbers%20%26amp%3B%20Dates%20Right%20aligned%20(That's%20the%20default)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-832335%22%20slang%3D%22en-US%22%3ERe%3A%20ARRAY%3F%3F%3F%20IDENTIFYING%20MULTIPLE%20CONDITIONS%2C%20DIVIDING%20ONE%20CONDITION%20AND%20THEN%20ADDING%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-832335%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F401325%22%20target%3D%22_blank%22%3E%40pfmartens%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20agree%20with%20Nabil%2C%20merged%20cells%20are%20to%20be%20avoided%20and%20as%20a%20rule%20not%20used.%20However%2C%20one%20more%20variant%20of%20the%20formula%20on%20source%20file%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20640px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F129529i3645BD8216BFA923%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eis%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUMPRODUCT(%20(%24A%242%3A%24A%2410%3D%22non-qualified%22)*%24G%242%3A%24G%2410*%24D%242%3A%24D%2410)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-832519%22%20slang%3D%22en-US%22%3ERe%3A%20ARRAY%3F%3F%3F%20IDENTIFYING%20MULTIPLE%20CONDITIONS%2C%20DIVIDING%20ONE%20CONDITION%20AND%20THEN%20ADDING%3F%3F%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-832519%22%20slang%3D%22en-US%22%3E%3CP%3EYOU%20ALL%20ARE%20INCREDIBLY%20AWESOME%2C%20AND%20THANK%20YOU%20ALL%20FOR%20YOUR%20PROMPT%20REPLY%20AND%20ADVISE!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThose%20cells%20and%20columns%20I%20attached%20are%20part%20of%20a%20three%20page%20form%20taking%20in%20a%20lot%20of%20information%20throughout%20the%20document.%26nbsp%3B%20I%20am%20not%20a%20power-user%20by%20any%20means%2C%20but%20I%20guess%20one%20might%20say%20I%20know%20enough%20to%20be%20dangerous%20or%20as%20what%20people%20who%20do%20know%20what%20they%20are%20doing%20might%20call%20reckless%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20able%20to%20get%20everything%20to%20work%20except%20for%20this%20last%20calculation%2C%20but%20once%20I%20finish%2C%20I%20will%20post%20a%20the%20entire%20document%20should%20anyone%20want%20to%20tell%20me%20how%20I%20could%20have%20done%20it%20better%20based%20on%20all%20the%20information%20I%20was%20trying%20to%20capture.%26nbsp%3B%20Thanks%20again%20to%20you%20all!%3C%2FP%3E%3C%2FLINGO-BODY%3E
pfmartens
New Contributor

Hi All,

I want find one condition sum it and divide by 2

Find all NON-QUALIFIED with 50% Ownership, then add up those account balances and then divide by 2

Find all NON-QUALIFIED with 100% Ownership and add those account balances.

Then, add those two results together and with the final result appearing in one cell.

Thanks in advance to anyone that can solve this...

5 Replies
P.S. The answer should be $1,272,355.07

@pfmartens 

Hi 

You have a problem with your setup because you are merging cells ABC, DEF, GHIJ... 

that's not the best setup and it negatively impacts your calculations

We need to fix this issue first and then create the function

So I made some very basic changes in your setup by UNMERGING all cells. BTW, it requires more changes.

See attached file

and I copied the Criteria to columns E & F

I created a 2 SumIfs functions in a random cell E6

The Function is

=SUMIFS(C2:C10,A2:A10,E2,B2:B10,F3)/2+SUMIFS(C2:C10,A2:A10,E2,B2:B10,F2)

The result should be:  $ 1,271,675.55

 Unmerge.png

 

Check the attached file

Also avoid Merge and Center command it has lots of problems. Watch this video about the alternate command

https://www.youtube.com/watch?v=JHBNp8YAT0w

Remember also that ALIGNMENT is functional in EXCEL (Unlike Word & PowerPoint) so make sure you keep numbers & Dates Right aligned (That's the default)

 

Hope that helps

Nabil Mourad

 

This is a Bite-Size Excel Tutorial Series (less than 3 minutes) targeting anyone on the globe using Excel since I broke the barrier of language and I explain the topic fully in graphics. The Merge and Center command on the Home Tab, centers a Title but at the same time it comes with many problems

@pfmartens 

I agree with Nabil, merged cells are to be avoided and as a rule not used. However, one more variant of the formula on source file

image.png

is

=SUMPRODUCT( ($A$2:$A$10="non-qualified")*$G$2:$G$10*$D$2:$D$10)

YOU ALL ARE INCREDIBLY AWESOME, AND THANK YOU ALL FOR YOUR PROMPT REPLY AND ADVISE!!!

 

Those cells and columns I attached are part of a three page form taking in a lot of information throughout the document.  I am not a power-user by any means, but I guess one might say I know enough to be dangerous or as what people who do know what they are doing might call reckless???

 

I was able to get everything to work except for this last calculation, but once I finish, I will post a the entire document should anyone want to tell me how I could have done it better based on all the information I was trying to capture.  Thanks again to you all!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies