Results 1 to 5 of 5

Thread: formula help

  1. #1
    Scottavs
    Guest

    formula help

    Need some help please. Enclosed is a small spreadsheet that I have been working on. I can not get a count function to work properly in Z13. I wish to count the number of entries in column N, if column E=1. The correct answer should be 5. I keep getting a total count or zero. I am sure I am missing the obvious here. Could someone help?
    Thank You!

    Scott Vander Sande

    bears@new.rr.com

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: formula help

    Hi Scott,
    I am assuming you only want to count the cells in N with a value where the corresponding cell in E has a 1? (in which case the answer is 3 not 5 by my reckoning). If so then type:
    =SUM(IF(E1:E12=1,IF(N1:N12<>"",1,0),0)) into N13 and array-enter it by pressing Ctrl-Shift-Enter simultaneously.
    Otherwise you're just counting the cells in E that have 1 in them ( =countif(E1:E12,1) )
    Hope that helps?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help

    Try this:

    =SUM((E2:E17=1)*(ISBLANK(N2:N17)=FALSE))

    I counted and got 6

  4. #4
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula help

    I get 6 entries.
    =SUMPRODUCT(N(N1:N10000>0),N(E1:E10000=1))
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

  5. #5
    Scottavs
    Guest

    Re: formula help

    Thanks for the help everyone. I got to see three different approaches that I had never seen before.

    Scott

Posting Permissions

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