View Poll Results: IF AND OR Statement, not working need help

Voters
0. This poll is closed
  • IF AND or OR function is not giving correct result, need help

    0 0%
  • IF Logical function

    0 0%
Multiple Choice Poll.
Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 30 of 34

Thread: IF AND OR excel statement

  1. #21
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,296
    Articles
    0


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by AliGW View Post

    1. You do not need a + sign at the start of this forumla (I don't know why you added one, but it is not necessary).
    Remnants from Lotus123
    Thank you Ken for this secure forum.

  2. #22
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    856
    Articles
    0
    Excel Version
    Office 365 Subscription
    Ah, OK - that explains it.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #23
    Yes. It did. Thank you so much. but would you explain how it works with your formula? so i can understand the following?. the layout may change in future dont know when.
    1. IF(SUM(COUNTIF($D5:$AA5,{"C","Z",""}))
    2. -COUNTIFS($D$4:$AA$4,"Student",$D5:$AA5,"")>=12,"Complete","No")


    Edit mod : removed part of the post to create a new thread
    Thank you again.
    Last edited by Pecoflyer; 2018-04-11 at 07:28 AM.

  4. #24
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,296
    Articles
    0
    @ NK123

    When you have a new question it's always best to start a new thread adding links to relevant posts as necessary.
    This will get you faster answers as more people will see it.
    I deleted your new question from post #23 and started a new thread for you
    Last edited by Pecoflyer; 2018-04-11 at 07:25 AM.
    Thank you Ken for this secure forum.

  5. #25
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    856
    Articles
    0
    Excel Version
    Office 365 Subscription
    If you change the layout, then the second formula is likely to be broken. However:

    =SUM(COUNTIF($D5:$AA5,{"C","Z",""}))

    This looks at the range D5:AA5 and returns the number of cells in that range that contain C or Z or are blank.

    -COUNTIFS($D$4:$AA$4,"Student",$D5:$AA5,"")

    This counts the number of cells in the range D5:AA5 that are blank if the corresponding cell in the range D4:AA4 says Student. This is then subtracted from the first number, as we don't want to include these in the IF statement.

    =IF( ... >=12,"Complete","No")

    If formula 1 minus formula 2 is bigger than or equal to 12, return Complete, If not, return No.


    Last edited by AliGW; 2018-04-11 at 02:00 PM.
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #26
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    856
    Articles
    0
    Excel Version
    Office 365 Subscription
    Pecoflyer was right to move your new query to a new thread, as it bears no relationship whatsoever to this one. I can't help with the new query - sorry.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #27
    Thank you AliGW and Pecoflyer.

  8. #28
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    856
    Articles
    0
    Excel Version
    Office 365 Subscription
    You're welcome!
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #29
    AliGW,

    Could you please help me on this?
    Detail tab
    I want to count: C, Z, O and I, but I do not want to count #N/A, in column “STATUS”.
    C, Z, O, I and #N/A can repeat in status column for JEN.... later on the data will be added in col. V onward to AC (in status and month col).
    Summary Tab

    1. The results (counted number, except #N/A) should be in the column E. this col represent completed/ incomplete/outstanding.
    2. Also under the column F only count C, Z. this represent completed

    #N/A represents no transactions, therefore should not be included in counting.

    Manually I have filled the results for row 3 to 14 in col E and F
    Column JEN.. in both tabs are unique.

    Thank you in advance!!
    Attached Files Attached Files

  10. #30
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    856
    Articles
    0
    Excel Version
    Office 365 Subscription
    You just need to add another exclusion criterion:

    =IF(SUM(COUNTIF($F6:$AC6,{"C","Z",""}))-COUNTIFS($F$5:$AC$5,"Status",$F6:$AC6,"",$F6:$AC6,NA)>=12,"Complete","No")
    Ali
    Enthusiastic self-taught user of MS Excel!

Page 3 of 4 FirstFirst 1 2 3 4 LastLast

Tags for this Thread

Posting Permissions

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