Excel 2007 : Formula that will return a value based on the values in multiple cell

My column headers have product names. The rows have the number of each product installed at a specific location.

I need to return a TRUE value if columns A:L and columns N:AO ALL contain the number 0 AND column M contains a 1.

I tried an IF AND written like this:

Please Login or Register  to view this content.

And I get a #VALUE error.

Also tried:

Please Login or Register  to view this content.

Same #VALUE error

What am I doing wrong?

Thanks!!

What do you mean by ALL?

Are you checking each of those columns within a row? Or do you mean every single cell in the entire columns have 0's in them?...

I am going to assume you misunderstood me an assume that you are look at a row per row basis (otherwise, you are looking a looking for 65600+ cells * that many number of columsn full of 0's.. which I am not sure that is what you want).

So, say we are in row 1.

=AND(SUM(F1:L1)=0,SUM(N1:AO1=0),M1=1)

will return TRUE if you have 0's or blanks in F1:L1 and N1:A01 and M1 = 1.

If you absolutely need to look for 0s, then

=AND(COUNTIF(F1:L1,0)=COLUMNS(F1:L1),COUNTIF(N1:AO1,0)=COLUMNS(N1:AO1)=0,M1=1)

copied down.

adjust if first row number is not 1.

=

I put a closing bracket in the wrong place...


=AND(SUM(F1:L1)=0,SUM(N1:AO1)=0,M1=1)