# 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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### SIXTH SENSE

##### Well-known Member
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

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

##### Active Member
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
25
Views
1K
Replies
0
Views
529
Replies
5
Views
706
Replies
8
Views
512
Replies
1
Views
120

1,171,790
Messages
5,877,506
Members
433,264
Latest member
Donnybrook

### 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