# What's wrong with the formula

#### xl_psychic

##### Active Member
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.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.

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.

SIXTH SENSE said:
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

Replies
9
Views
296
Replies
1
Views
199
Replies
3
Views
357
Replies
1
Views
44
Replies
7
Views
295

1,216,496
Messages
6,130,985
Members
449,612
Latest member
geniusufo007

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back