Results 1 to 10 of 10
  1. #1
    New Lounger
    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.

  2. #2
    Platinum Lounger
    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 control-shift enter when entering the formula)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Counting (97 & 2000)

    {=SUM(((D514+E5:E14)<0)*1)} also works.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    3 Star Lounger
    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}

    Aladin
    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    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

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

  7. #7
    3 Star Lounger
    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,

    Aladin
    Microsoft MVP - Excel

  8. #8
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Silver Lounger
    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

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 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

Posting Permissions

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