# Thread: Counting (97 & 2000)

1. ## Counting (97 & 2000)

Have 2 columns of figures (temperatures) & I would like excel to count how many of the said temperatures are negative, that I can do BUT (always a but) this is what I would like the formula to recognise & count - if 2 temperatures are negative in ajoining cells, then count that as one. ie say a1 & b1 both have a -ve value then excel to count one for row 1.The max count for any row has to be one. Hope I have explained this okay. cheers andrew macdonald.

2. ## Re: Counting (97 & 2000)

=SUM((((A1:A10<0)+(B1:B10<0))>0)*1)

(press control-shift enter when entering the formula)

3. ## Re: Counting (97 & 2000)

{=SUM(((D514+E5:E14)<0)*1)} also works.

4. ## Re: Counting (97 & 2000)

I don't think your version

{=SUM(((D514+E5:E14)<0)*1)}

does work the same as thart of Jan Karel, which is:

{=SUM((((A1:A10<0)+(B1:B10<0))>0)*1)}

Their logical structure is different,

Try both on the following sample:

{-1,1;
1,1;
-2,-4;
-4,3;
-3,4}

5. ## Re: Counting (97 & 2000)

Looking at the subsequent posts on the thread, I agree with Aladin that John's solution doesn't do the same as Jan's. The case of {-1,1} on the same row results in cancellation and is not counted. The case of {4,-3} results in the positive swamping out the negative and is not counted either.

For my 2 cents, here's another solution:
{ =SUM(IF(A1:A6>=0,IF(B1:B6>=0,0,1),1)) }

This may be more intuitve but Jan's is more extensible (IF's can only be nested up to, I think, 7 tests).

Fred

6. ## Re: Counting (97 & 2000)

You're right. My testing was inadequate. Mine was wrong, and I don't think Jan Karels' was what the poster wanted, which was:

=SUM(((A1:A20<0)*(B1:B20)<0)*1)

fburg's looks correct, but doesn't test out from what I think the oringinal poster wanted. See attachment.

7. ## Re: Counting (97 & 2000)

John --

I think there are 2 possible readings of what the original poster wanted:

(a) ORing and another
([img]/forums/images/smilies/cool.gif[/img] ANDing.

I'm sure Jan Karel was also aware of this. He simply opted for reading (a).

In array or sumproduct formulas + does the ORing, * the ANDing. Your first attempt was altogether a different thing (which prompted my reply). Fred gave an array formula with IFs, equivalent to Jan Karel's which is constructed with Boolean terms. If the latter reading would be what the OP wants, then

{=SUM((A1:A20<0)*(B1:B20<0))

would be indeed the answer.

By the way, these two formulas have the following, normally entered, equivalents with sumproduct:

=SUMPRODUCT((((A1:A10<0)+(B1:B10<0))>0)+0)

=SUMPRODUCT((A1:A20<0)*(B1:B20<0))

Regards,

8. ## Re: Counting (97 & 2000)

I think there is only *one* possible explanation of what the original poster wanted.
He wanted to count one, when there is either one *OR* two negative readings. Which is clearly an OR, not an AND.

9. ## Re: Counting (97 & 2000)

Hi Jan Karel,

Since you're the last poster on this, I guess I'll respond to you.

It was good for John to put all the solutions together in 1 sheet. That way, the originator can pick what they want.

I read the original post as wanting to count a row if:
- both cols were negative, or
- one col was negative

Or put another way, count the rows where there is at least one col with a neg number. So I disagree with John that this should be an AND condition as posted in his latest. The way I constructed the IF did provide an OR.

Anyway, whether it's AND or OR, John's workbook has them all.

Fred

10. ## Re: Counting (97 & 2000)

I definitely read the poster as wanting when both columns on one row were negative. But now he has a choice.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•