Formula needed to split the results of a DSUM from another worksheet

Hello ~

Describing this may be complex:

Main1 is the main worksheet which contains a ton of information other than what I'm showing you but I have simplified it here. Cells A5 through A9 and A20 through A24 on this worksheet is where I need help. The formula I am currently using is not pulling the information how I need it to be pulled.

Report A is an example of how one of our reports look when we print to excel. I would like this to remain unmodified if possible to limit the work done by others. The "Amount" column doesn't matter here - and the numbers in my example don't make any sense - the focus should be on the % of Totals column.

So the report prints out the codes - CAAA for example and CAAAZZ along with many others (could be hundreds). I need the formula on Main1 to correctly sum the amounts seperately for each type. So in this example AAAA in Column B of Main1 shows 15 percent. That is the total of all of the AAAA codes, but I need it to sum all the regular codes minus the ZZ for cells A5 through A9 and sum all the "ZZ" codes for cells A20 through A24. I need it to show cell A5 as "10" and cell A20 as 5, for example.

Any help is appreciated! Thank you!

sample1.xls

Welcome to the forum.

Would be helpful if you can upload a sample workbook.

Not sure if this entirely addresses your points, but try this:

In cell A5:

Formula:

Please Login or Register  to view this content.

In cell A20:

Formula:

Please Login or Register  to view this content.

The above gives 10 in cell A5 and 5 in cell A20, as you had mentioned in the original post.

Hope this helps.

Replace A5 with below and drag the formula down till A10:

Formula:

Please Login or Register  to view this content.

Cell A7 now has 7.95 against CAAA. Hope thats what you wanted.

No worries