the spaces are not showing, the table in my example should look like this:
A B C D E F
<space <space <space "Not Applicable" "No Allowance Made"
1 2 3 TRUE TRUE
4 5 6 TRUE <space
7 8 9 <space TRUE


"Yong Heng" wrote:

minor correction, it should be:

A B C D E F
"Not Applicable" "No Allowance Made"
1 2 3 TRUE TRUE
4 5 6 TRUE
7 8 9 TRUE

not applicable and no allowance made
=SUMPRODUCT(--(D2:D4)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 6 (1x2x3)

not applicable ONLY
=SUMPRODUCT(--(D2:D4)*NOT(E2:E4),A2:A4,B2:B4,C2:C4)
result = 120 (4x5x6)

no allowance made ONLY
=SUMPRODUCT(--NOT(D2:D4)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 504 (7x8x9)

"Yong Heng" wrote:

Hi,

It'll be helpful if u could provide some sample data.

However, looking at your formula, you are trying to perform arithmetic
operations on G35 which could have a value of "No Allowance Made"

You may be better off using sumproduct():

A B C D E F
"Not Applicable" "No Allowance Made"
1 2 3 TRUE TRUE
4 5 6 TRUE
7 8 9 TRUE

not applicable and no allowance made
=SUMPRODUCT(--(D2:D4)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 6 (1x2x3)

not applicable ONLY
=SUMPRODUCT(--(D2:D4)*NOT(E2:E4),A2:A4,B2:B4)
result = 20 (4x5x6)

no allowance made ONLY
=SUMPRODUCT(--NOT(D2:D4)*(E2:E4),A2:A4,B2:B4,C2:C4)
result = 504 (7x8x9)

Yong Heng
Singapore

"John G - HTW" wrote:

I've just created a nested/embedded logical formula, which appears okay
(i.e., with out errors), and the Function Arguments dialogue box displays an
answer. However, the cell still displays zero and I don't understand why.

If it's of any assistance, I have included the formula below:
=ROUND(IF(AND(G34="Not Applicable",G35="No Allowance Made"),G36,IF(G34="Not
Applicable",G36+G35,IF(G35="No Allowance Made",G36-G34,G36+G35-G34))),0)

I've checked my formatting and this appears okay. Does anyone have any
explanation as to why my result is not displaying?