Calculating multiple values from merged cells with if then statement

[aumdean]

I have a large spreadsheet I have created an example from. I have Names listed in Column A, Amounts listed in Column B, and a dropdown validation list in column C with 4 different text options.

At the bottom of this table, I have calculation summaries that check the dropdown validation listed, and sums the total amounts for that option listed in column C. However, I need an If Then statement that prioritizes the calculations so that certain amounts pertaining to the Names in Column A are not calculated several times.

I have an example for reference, this is difficult to explain - however I do not see an option to attach an example. I realize that the merged data in columns A & B makes this more difficult, but would like to leave this as is if possible. I can use an additional hidden column for listing individual amounts for each row if needed.

 

[AD_Taylor]

I don't think you can upload files to this forum for some reason. PM me and I'll send you my email that you can forward the file to if you like.

 

[AD_Taylor]

I've had a chance to look over the example file you've sent and as far as I can tell it doesn't need anything doing to it?

Can you try and rephrase your question as I might not be understanding it correctly?

 

[aumdean]

I have added a sceenshot of the original file from which the example was created. I realized from further testing that you cannot utilize merged cells within a table, therefore the example I sent you isn't relevant. I have attached a screen shot of the spreadsheet in its original form.

You will see in Column A-D, there are rows containing duplicate data. These were the rows that I previously had merged, mainly just for formatting purposes. The first issue is the calculation in D:29 - I need this formula to only calculate unique values listed in D6:D25 rather than counting duplicate values and inflating the total amount. Formula currently used: =SUM(D6:D25)

The next issue deals with the data validation selections in Column F6:F25. There are 4 text selections in a dropdown list. The summation boxes at the bottom labeled "Positive", "Negative", "No Policy Posted" and "Not Mentioned" use the following formula: =COUNTIF(F6:F25, "Negative"). This calculation is okay as I need to know the total number of coverage status indications even if there are duplicates. However, the "Lives" summation adjacent to the "Policy" summation in G:29 uses the following formula: =SUMIF(F6:F25, "Negative", $D$6:$D$25). I need this formula to only calculate the value once for duplicate entries in Column A. As it is now, the totals listed are inflated due to being counted for each data validation selection.

I hope I have explained this more clearly. I can forward the actual excel file to anyone willing to help come up with a solution. I prefer a formula over a macro, but will take whatever is needed to produce the desired result.

 

[aumdean]

Also, if there is a method to accomplish the above while using merged rows in Columns A-D for duplicate entries, that would be incredible. Many thanks!

 

[AD_Taylor]

If you can send me the workbook that you've posted that screenshot from, I'm sure I can send back something using merged cells and with the right formulas. I have a plan in mind that I've used before with merged cells.

If there is sensitive data, you could just change some of it to random names/numbers?