Aug 30 2019 04:17 PM
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...
Aug 30 2019 05:10 PM
Aug 30 2019 09:49 PM
Aug 30 2019 11:58 PM - edited Aug 31 2019 12:19 AM
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
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
Aug 31 2019 03:51 AM
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
is
=SUMPRODUCT( ($A$2:$A$10="non-qualified")*$G$2:$G$10*$D$2:$D$10)
Aug 31 2019 11:53 AM
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!