What's wrong with the formula

[xl_psychic]

Hi,

Im using the following formula to give me the total for all the cells matching the three criterias. However it doesn't seem to work? What am i doing wrong over here.

Here's the formula :

(SUMPRODUCT(--([TL1.xls]TL1!$H$11:$H$27="QMA - India"),--([TL1.xls]TL1!$J$11:$J$27=1),--([TL1.xls]TL1!$P$11:$P$27="Witness"),--([TL1.xls]TL1!$I$11:$I$27))

Where Column H contains Evaluator
Column J contains Week
Column P contains Type of Monitoring
Column I is the Score.

 

[SIXTH SENSE]

hi!
I the formula simply counts the occurence of the criteria that is true. if you want to sum up column I remove the "--" in it!
i hope I got you right or post this data using colos html maker.

 

[Aaron Blood]

The formula worked fine when I tested it.
A couple things you could check.

1. Are the values you're testing imported from a database? Therefore, they might be entered as text and be returning value of zero instead of a number.

2. Make sure the text entries in columns H & P do not include leading or trailing spaces.



And just as a side note...

I found it interesting that you used the double negative to help seperate the formula into its sections. Although, I usually just insert 4 or 5 blank spaces to accomplish the same thing.

 

[xl_psychic]

hi!
I the formula simply counts the occurence of the criteria that is true. if you want to sum up column I remove the "--" in it!
i hope I got you right or post this data using colos html maker.

Thanks a lot. Worked just fine for me. Wonder why i didn't go ahead and do it myself.... silly me...

Thanks ne ways