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

[pfmartens]

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...

[#2]

P.S. The answer should be $1,272,355.07 :)

[#3]

@pfmartens 

I got a different result.

See attached file.

[#4]

@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

 

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

[#5]

@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

is

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

[#6]

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!