Counting (97 & 2000)

Topic

New Reply

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.

Reply | Quote

Replies

How about this array formula:

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

(press control-shift enter when entering the formula)

Reply | Quote

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

Reply | Quote

I don’t think your version

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

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

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

Their logical structure is different,

Try both on the following sample:

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

Aladin

Reply | Quote

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.

Reply | Quote

John —

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

One reading implicates

(a) ORing and another
( 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:B100)+0)

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

Regards,

Aladin

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote