What's wrong with the formula

xl_psychic

Active Member
Joined
Jan 4, 2004
Messages
359
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top