Results 1 to 10 of 10
Thread: Counting (97 & 2000)

20020118, 06:32 #1
 Join Date
 Oct 2001
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20020118, 07:48 #2
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting (97 & 2000)
How about this array formula:
=SUM((((A1:A10<0)+(B1:B10<0))>0)*1)
(press controlshift enter when entering the formula)Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20020118, 17:43 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
Re: Counting (97 & 2000)
{=SUM(((D514+E5:E14)<0)*1)} also works.
John ... I float in liquid gardens
UTC 7ąDS

20020119, 15:25 #4
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
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}
AladinMicrosoft MVP  Excel

20020120, 13:07 #5
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
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

20020120, 23:54 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
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.John ... I float in liquid gardens
UTC 7ąDS

20020121, 05:45 #7
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Counting (97 & 2000)
John 
I think there are 2 possible readings of what the original poster wanted:
One reading implicates
(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,
AladinMicrosoft MVP  Excel

20020121, 06:09 #8
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20020121, 12:39 #9
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,921
 Thanks
 6
 Thanked 9 Times in 7 Posts
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

20020121, 16:25 #10
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 5 Times in 5 Posts
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.
John ... I float in liquid gardens
UTC 7ąDS