Help on complex multi-column sum is appreciated
 

I would appreciate some direction on how to perform the following complex
sum. I expect it to have several subtotals, and maybe a sumproduct that are
added and subtracted correctly.

I have the following cell structure.

A B C D
-------------------------
11 Z $45.31
12 $2.31 $25.00
13 $18.00
14 $15.00
15 $20.00 $20.00
16 $1.44 $15.56 $17.00

The columns are defined as the following:

A - Mark a row as the sum of several other cells.
B - Sales Tax
C - Item Cost
D - Total Charge

For the 'Z Mark in col A in the example above, D11 is the sum of B12:C13.

The total sum would have the following rules:

- if C has a value, include B and C in sum and don't include D (or subtract
it out).
- if C is blank include D in sum
- Do not include D if Col A has capital letter.

The purpose is to use this calculation to generate a total sum of the
filtered charges. In this example the total sum should be $97.31. But if
autofilter hides say rows 13 and 14, the total $64.31. The total is displayed
in say cell D5

Any ideas,

Thanks a bunch,
JOhn