+ Reply to Thread
Results 1 to 6 of 6

Using IF to return a Count based on specific cell values.

  1. 07-04-2019, 01:00 PM #1
    konkonmike
    konkonmike is offline
    Registered User
    Join Date
    07-04-2019
    Location
    Venezuela
    MS-Off Ver
    2019
    Posts
    6

    Using IF to return a Count based on specific cell values.

    Good Morning Guys,

    I have 1 Column from A1:A1000 with Country names, then I have a column D1:D1000 with number values like 20.5, 100, 95.7 etc etc. I want bring the total amount cell within 0-49 from each country, for example:

    Between 0 and 49

    Brazil 29
    Guatemala 31

    Between 50 and 89

    United States 23
    Canada 78

    Would appreciate any help on this. This is the formula i have been trying to make work with no luck, im not so fluent with excel. =IF(A1:A1000="Canada",COUNTIFS(D1:D1000,">=0%",D1:D1000,"<=49.99%"))

    Thank in advance guys.
    Reply With QuoteRegister To Reply
  2. 07-04-2019, 03:31 PM #2
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Using IF to return a Count based on specific cell values.

    Welcome to the forum.

    If I understand correctly what you are trying to do, you should put all the criteria into the COUNTIFS function. Something like this:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    I've put <50 instead of <=49.99, as this allows for figures with hidden rounding, like 49.991.

    Also, if you have your list of countries (for which you want the results, not the col A list) in another area, you could refer to there instead of putting them directly into the formula. For example, if you have the countries in column F, starting in F2, you could put this in G2 and drag down:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Attached is a file showing this second formula working.

    Hope that helps.
    Attached Files Attached Files
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.
    Reply With QuoteRegister To Reply
  3. 07-04-2019, 03:37 PM #3
    konkonmike
    konkonmike is offline
    Registered User
    Join Date
    07-04-2019
    Location
    Venezuela
    MS-Off Ver
    2019
    Posts
    6

    Re: Using IF to return a Count based on specific cell values.

    Aardigspook appreciate the time for clarifying and explaining as detailed as you did. Will put this to work right away and post results. Thank you.
    Reply With QuoteRegister To Reply
  4. 07-04-2019, 03:55 PM #4
    Aardigspook
    Aardigspook is offline
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Using IF to return a Count based on specific cell values.

    You're welcome. If it doesn't work with your real file, then just let me/us know and we can suggest something else.
    Reply With QuoteRegister To Reply
  5. 07-05-2019, 12:09 AM #5
    konkonmike
    konkonmike is offline
    Registered User
    Join Date
    07-04-2019
    Location
    Venezuela
    MS-Off Ver
    2019
    Posts
    6

    Using IF to return a Count based on specific cell values.

    It worked perfectly. Managed to use it with Countries in a single column then just dragging the formula. Appreciate it.
    Attached Images Attached Images
    Last edited by AliGW; 07-05-2019 at 01:40 AM. Reason: Please don't quote unnecessarily!
    Reply With QuoteRegister To Reply
  6. 07-05-2019, 01:40 AM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,762

    Re: Using IF to return a Count based on specific cell values.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Return values to specific columns based on range of cell values
    By sbrt10 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-27-2019, 03:15 AM
  2. [SOLVED] Populate values based on specific count
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 05-06-2016, 10:41 AM
  3. Look up several values in a cell and return a specific text
    By peterromer in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-27-2014, 12:03 PM
  4. [SOLVED] Return cell count based on multiple values (and data validation)
    By Reapz in forum Excel General
    Replies: 3
    Last Post: 01-10-2014, 06:05 AM
  5. [SOLVED] Return specific values based on the range in another cell
    By AndrewBondarchuk in forum Excel General
    Replies: 3
    Last Post: 11-28-2012, 12:34 PM
  6. Store cell values in an array and return values on specific condition
    By gmalpani in forum Excel - New Users/Basics
    Replies: 7
    Last Post: 11-27-2011, 06:43 AM
  7. Count column w/blanks & values based on specific date
    By nickelcell1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-30-2009, 04:08 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1