Results 1 to 10 of 10

Thread: COUNTIF With Multiple Criteria Across Multiple Columns

  1. #1
    Junior Member
    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0

    Question COUNTIF With Multiple Criteria Across Multiple Columns

    Need help with the below formula. Not sure what the problem is since its returning 0 when it should return 2 since there are two rows that meet the four criterias that I'm looking for (W85NAA, OD, 3A., and MSG). Im using this as an example since I have a huge list with all the criterias below.

    B1=W85NAA
    B2=W73P01
    B3=W9G9AA
    B4=WSR7AA

    K1=DUE
    K2=OD

    U1=1A. Pending Separation
    U2=2A. Discharged
    U3=3A. Pending Loss
    U4=4A. Medical
    U5=5A. Transfer

    E1=CSM
    E2=SGM
    E3=MSG
    E4=SFC
    E5=SSG
    E6=SGT

    A1=SUMPRODUCT(COUNTIFS(Master!$B$1:$B$4,"*W85NAA*" ,Master!$K$1:$K$2,"*OD*",Master!$U$1:$U$5,{"*1A*", "*2A*","3A*","4A*","5A*","6A*","7A*"},Master!$E$1: $E$6,{"SGM";"CSM";"1SG";"MSG";"SFC";"SSG";"SGT"}))
    Last edited by GymWrecker; 11-10-2013 at 09:49 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    W85NAA is in the 1st Row
    OD is in the 2nd Row
    3A. and MSG is in the 3rd Row

    How do you say that you should get 2 as result?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0
    Excel Fox, here's the actual spreadsheet. Under the Tab Summary you can find the formula(s) that I need help with (highlighted in yellow). Hope this helps.

    https://app.box.com/s/xgnkmsc89871n6gaf6st
    Last edited by GymWrecker; 11-10-2013 at 11:54 PM. Reason: added link to box

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try this one

    =SUMPRODUCT(ISNUMBER(SEARCH("W7ZPB1",Master!$B$1:$ B$300))*ISNUMBER(SEARCH("OD",Master!$K$1:$K$300))* ISNUMBER(SEARCH("9",Master!$U$1:$U$300))*ISNUMBER( SEARCH({"MG","BG","COL","LTC","MAJ","CPT","1LT","2 LT","CW4","CW3","CW2","WO1"},Master!$E$1:$E$300) ))

    I've used this from cell F19
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0
    Excel Fox, the formula still missing one more criteria: Master!$U$1:$U$300,{"*1A*","*2A*","3A*","4A*","5A* ","6A*","7A*"}, I think once I get this criteria embeded into the formula it should work.

    =SUMPRODUCT(ISNUMBER(SEARCH("W7ZPB1",Master!$B$1:$ B$300))*ISNUMBER(SEARCH("OD",Master!$K$1:$K$300))* ISNUMBER(SEARCH("9",Master!$U$1:$U$300))*ISNUMBER( SEARCH({"MG","BG","COL","LTC","MAJ","CPT","1LT","2 LT","CW4","CW3","CW2","WO1"},Master!$E$1:$E$300) ))

    Thanks again!

  6. #6
    Junior Member
    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0
    My bad, I see that the range U1:U300 is in the formula. The only problem is that instead of "9", it should have the following criteria:*1A*","*2A*","3A*","4A*","5A* ","6A*","7A*

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    So in place of "9", use {"1A","2A","3A","4A","5A","6A","7A"}
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Or even better, use {"1A.","2A.","3A.","4A.","5A.","6A.","7A."}
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Junior Member
    Join Date
    Apr 2013
    Posts
    8
    Rep Power
    0
    Well, I replaced "9" with the right criteria, but I'm now getting an error "N/A"

    This is the formula I have in
    F19=SUMPRODUCT(ISNUMBER(SEARCH("W7ZPB1",Master!$B$ 1:$B$300))*ISNUMBER(SEARCH({"*1A.*","*2A.*","*3A.* ","*4A.*","*5A.*","*6A.*","*7A.*"},Master!$K$1:$K$ 300))*ISNUMBER(SEARCH({"MG","BG","COL","LTC","MAJ" ,"CPT","1LT","2LT","CW4","CW3","CW2","WO1"},Master !$E$1:$E$300)))
    Last edited by GymWrecker; 11-11-2013 at 12:52 AM.

  10. #10
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Please remove the asteriks. You don't need that.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 6
    Last Post: 08-14-2013, 04:25 PM
  2. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  3. Replies: 7
    Last Post: 05-15-2013, 02:56 PM
  4. Replies: 3
    Last Post: 03-12-2013, 12:54 PM
  5. Replies: 2
    Last Post: 06-14-2012, 04:10 AM

Posting Permissions

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