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.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

SIXTH SENSE

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
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
Joined
Oct 10, 2002
Messages
485
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
Joined
Jan 4, 2004
Messages
359
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,196
Members
414,513
Latest member
junbuggle

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
Top