Excel formula problem 1

[Bill4tektips]

I have a spreadsheet and I need to create a Graph. In column J I have cells containing number and text in the format "1.1 Discussion Group", in column K & L I have numbers and I need to sum the total when 3 criteria are met. I have tried this formula: "=SUMPRODUCT((LEFT('NCR_Data'!$J$2:$J$200,3)="1.1")*('NCR_Data'!$K$2:$K$200="1")*('NCR_Data'!$L$2:$L$200="0"))" but I can't get it to work. Any suggestions?

 

[GlennUK]

You've specified 3 criteria and no numbers to sum. What are you actually trying to sum? Or do you mean that you want to count where those conditions exist, maybe?

You currently should get a count of rows where:
'NCR_Data'!$J$2:$J$200 starts with "1.1" and
'NCR_Data'!$K$2:$K$200="1" and
'NCR_Data'!$L$2:$L$200="0"
( except that you are testing numbers against strings of "0" and "1" instead of 0 and 1 )


Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

If you actually have NUMBERS in columns J & K, then remove the QUOTES around your criteria!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}