+ Reply to Thread
Results 1 to 27 of 27

Countifs one criteria range multi dynamic cells as criteria

  1. #1
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Countifs one criteria range multi dynamic cells as criteria

    Anyone please help, how to use countif or countifs with one criteria range and multi cells as criteria in it. I need it cuz there are too many data n many dynamic cells as criteria... Idon't want the "TEXT" as criteria ({"Banana";"Cherry";"Melon"}) ... Thank You for help...

    COUNTIFS MULTI.JPG

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Countifs one criteria range multi dynamic cells as criteria

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    The file :


    COUNTIFS ONE CRITERIA RANGE MULTI CELL AS CRITERIA.xls


    https://www.excelforum.com/attachmen...1&d=1520671698
    Last edited by brake; 03-10-2018 at 04:50 AM.

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Countifs one criteria range multi dynamic cells as criteria

    In invalid attachment.

  5. #5
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    the insert attachmenst button doesn't work....
    but the Attachment file i've just replied works.... i can click on it n get the file....


    https://www.excelforum.com/attachmen...s-criteria.xls
    Last edited by brake; 03-10-2018 at 04:51 AM.

  6. #6
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Countifs one criteria range multi dynamic cells as criteria

    How you think that Grape should not be criteria ?

  7. #7
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    That's the example.... the real data should be complex... i have to fill in the xcel sheet template given, and there are three criteria i have to avoid (not count) in one range... like the example

  8. #8
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Countifs one criteria range multi dynamic cells as criteria

    Quote Originally Posted by brake View Post
    That's the example.... the real data should be complex... i have to fill in the xcel sheet template given, and there are three criteria i have to avoid (not count) in one range... like the example
    That is exactly I wish to ask how you thing to avoid Grape. What is logic behind it

  9. #9
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    Quote Originally Posted by shukla.ankur281190 View Post
    That is exactly I wish to ask how you thing to avoid Grape. What is logic behind it
    There isn't a thing written in the table to identify, i just asked to avoid it, for example, i must avoid "Grape" to be counted because the "Grape" is not needed, but the excel sheet cell exists

  10. #10
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Countifs one criteria range multi dynamic cells as criteria

    Perhaps you are not getting what I asked you, how do you decide which text need to avoid?

  11. #11
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    Okay, sorry then...

    I give another example....

    COUNTIFS MULTI2b.JPG

    The example shows there is Motors group column in the Fruits Column group... but i have to use it as it is, as its column order, my boss forbids me to chnge the column order...
    Last edited by brake; 03-10-2018 at 05:55 AM.

  12. #12
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Countifs one criteria range multi dynamic cells as criteria

    Try

    [FORMULA][/=SUMPRODUCT(COUNTIFS(A2:A27,C2:G2))-SUMPRODUCT(COUNTIFS(A2:A27,I2:L2))FORMULA]

  13. #13
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    Thx

    but is there another way to use just one SUMPRODUCT, like so: =SUMPRODUCT(COUNTIFS(A2:A27,AND(C2:G2,I2:L2)),(but this doesn't work)... cuz still there are too many criteria in the formula i have to add... that makes twice longer formula....

  14. #14
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    Quote Originally Posted by shukla.ankur281190 View Post
    Try

    [FORMULA][/=SUMPRODUCT(COUNTIFS(A2:A27,C2:G2))-SUMPRODUCT(COUNTIFS(A2:A27,I2:L2))FORMULA]
    sorry, but, the Honda in the Fruits group isn't counted

    Attachment 564988

  15. #15
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Countifs one criteria range multi dynamic cells as criteria

    Do updated excel sheet with expected result with all explanation

  16. #16
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria


  17. #17
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Countifs one criteria range multi dynamic cells as criteria

    Invalid attachment

  18. #18
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    Quote Originally Posted by shukla.ankur281190 View Post
    Invalid attachment
    https://www.excelforum.com/attachmen...criteria-b.xls

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Countifs one criteria range multi dynamic cells as criteria

    Try

    =SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$45,$C$3:$F$3)))

    BUT selections must be contiguous cells e,g in example C3:F3 contain "Banana Cherry Melon Carrot"

    =SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$45,$G$3:$M$3)))

    G3:M3 contain "Honda Jeep Toyota Yamaha Suzuki KTM Land Rover"


    And you have still not answered Shukla's question: how would we select from the lists in row 2

  20. #20
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    Quote Originally Posted by JohnTopley View Post
    Try

    =SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$45,$C$3:$F$3)))

    BUT selections must be contiguous cells e,g in example C3:F3 contain "Banana Cherry Melon Carrot"

    =SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$45,$G$3:$M$3)))

    G3:M3 contain "Honda Jeep Toyota Yamaha Suzuki KTM Land Rover"


    And you have still not answered Shukla's question: how would we select from the lists in row 2
    That's the case I get..... The list given is forbidden to change.... i have to follow the list in the row 2, that jump to another group... that's the static excel sheet i have to fill. The form in the excel sheet must be like so,

  21. #21
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Countifs one criteria range multi dynamic cells as criteria

    Actually Excel is not smart enough to recognise what is Motor or Fruit, so try to use extra row (row 3) to mark with F or M (Fruit/Motor)
    =SUM(COUNTIF($A$2:$A$45,IF(C3:O3="F",C2:O2,"")))
    Change F to M for Motor

    confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    See attachment....
    Quang PT

  22. #22
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    Quote Originally Posted by bebo021999 View Post
    Actually Excel is not smart enough to recognise what is Motor or Fruit, so try to use extra row (row 3) to mark with F or M (Fruit/Motor)
    =SUM(COUNTIF($A$2:$A$45,IF(C3:O3="F",C2:O2,"")))
    Change F to M for Motor

    confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    See attachment....
    Thx...
    If i change the order, or add another row to identify, problem solved... but nothing to change, nothing to add...

  23. #23
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Countifs one criteria range multi dynamic cells as criteria

    Bebo021999's reply is what we expected of you - i.e something to identify what to select.

  24. #24
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    is there anyway to change {"Banana";"Cherry";"Melon";"Carrot"} with cell ref in the formula below :

    =SUM(COUNTIF(A2:A45;{"Banana";"Cherry";"Melon";"Carrot"}))

    while the "Banana";"Cherry";"Melon" and "Carrot" inthe same Row but jumping column order

  25. #25
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Countifs one criteria range multi dynamic cells as criteria

    What is wrong with Bebo's solution?

  26. #26
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    Quote Originally Posted by bebo021999 View Post
    Actually Excel is not smart enough to recognise what is Motor or Fruit, so try to use extra row (row 3) to mark with F or M (Fruit/Motor)
    =SUM(COUNTIF($A$2:$A$45,IF(C3:O3="F",C2:O2,"")))
    Change F to M for Motor

    confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    See attachment....
    This's a good solution..... Thx.... If there is no way to get result without adding row to identify, I'll use it.... Thank you...
    Last edited by brake; 03-10-2018 at 08:29 AM.

  27. #27
    Registered User
    Join Date
    01-08-2017
    Location
    Malang, Indonesia
    MS-Off Ver
    2013
    Posts
    29

    Re: Countifs one criteria range multi dynamic cells as criteria

    Thx All, for your help, For the solution You share.... These help me much..... I do appreciate that..... Good Luck....

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Multi COUNTIFS criteria & ranges
    By zippyste in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-01-2018, 12:29 PM
  2. [SOLVED] COUNTIFS with Multiple Text Criteria in Same Criteria Range
    By xl2016 in forum Excel General
    Replies: 5
    Last Post: 06-18-2017, 05:00 AM
  3. Countifs with multiple criteria in single criteria range
    By SUHAS KARHADKAR in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 12-01-2016, 10:55 AM
  4. Countifs with multiple criteria in single criteria range
    By MCP313 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-01-2016, 03:51 AM
  5. [SOLVED] COUNTIFS with multiple criteria in the same criteria range
    By Faridwahidi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-23-2015, 01:04 PM
  6. Replies: 1
    Last Post: 08-09-2013, 10:30 AM
  7. Replies: 6
    Last Post: 02-21-2009, 08:13 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1