+ Reply to Thread
Results 1 to 3 of 3

How to correctly place a range of numbers in COUNTIFS and SUMIFS formulas?

Hybrid View

  1. #1
    Registered User
    Join Date
    06-07-2016
    Location
    Poland
    MS-Off Ver
    Microsoft Office 2007
    Posts
    4

    How to correctly place a range of numbers in COUNTIFS and SUMIFS formulas?

    Hello everybody!

    I am new here so I hope I won't accidentally breach any rules I tried searching but my problem is very difficult to look for because there are so many problems with different kinds of ranges.

    This is my problem:

    I want to place a formula =COUNTIFS which goes like this:

    =COUNTIFS('Sheet1 '!$F$2:$F$445;C88; 'Sheet2'!$V$2:$V$445;"15<=>=30")

    and I have a problem with this part: "15<=>=30"
    This is supposed to count the records only if in a range $V$2:$V$445 there are numbers: (15, 16, 17, 18, ...., 29, 30). I thought that this "15<=>=30" should work just fine but it didn't count correctly so it looks like I've made a mistake. Could someone please help me? Excel doesn't see a problem with this part of the formula, there are no errors but when I search manually through my spreadsheet, I can see a record matching my criteria but COUNTIFS doesn't see this record.

    How to correctly indicate in my formula that I am interested only in counting numbers that are "equal or greater than 15 AND equal or smaller than 30"?

    The first part of my formula (not in bold) works just fine I also don't have a problem when I want to count, for example, something greater than 30. Or something smaller than 15. But I can't correctly place a range of numbers.

    Thank you in advance for your help!

    Regards,
    Justyna
    Last edited by nerkaszatana; 06-07-2016 at 11:53 AM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How to correctly place a range of numbers in COUNTIFS and SUMIFS formulas?

    Hi,

    Are you really trying to count stuff across sheet1 & sheet2? or is that a typo and all the data is on the same sheet (either sheet1 or sheet2)?

    If the data is on two sheets and unless it's effectively an OR condition meaning you can add two COUNTIFS together then I don;t believe there's any straightforward way to have an effective AND Countif function across a 3D range.

    The Sheet2 COUNTIF alone would be

    Formula: copy to clipboard
    =COUNTIFS('Sheet2'!$V$2:$V$445;">=15",'Sheet2'!$V$2:$V$445;"<=30")
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-07-2016
    Location
    Poland
    MS-Off Ver
    Microsoft Office 2007
    Posts
    4

    Re: How to correctly place a range of numbers in COUNTIFS and SUMIFS formulas?

    Hello Richard,

    Thank you for your feedback! You are right, I was trying to conceal the real name of the sheet (this is for my work and I didn't want to post the name of the sheet on the Internet) and the count is across one sheet. It looks like I've forgotten about the most straightforward way to do this - to divide this range into two different, simpler argument.

+ 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. Using Countifs to ONLY COUNT numbers NOT FORMULAS
    By nightdawg in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 05-17-2016, 05:33 PM
  2. Adding corresponding values of a date range (SUMIFS & COUNTIFS)
    By Apol21 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-29-2016, 12:32 PM
  3. [SOLVED] Countifs and sumifs using Name Range
    By anh03 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-23-2015, 11:19 PM
  4. [SOLVED] I can countifs a range but not sumifs
    By port in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-18-2014, 07:06 PM
  5. [SOLVED] Complex if Formulas AVERAGEIFS, SUMIFS, COUNTIFS
    By fredlo2010 in forum Excel General
    Replies: 5
    Last Post: 06-19-2014, 03:26 PM
  6. Countifs and Sumifs VBA code with criterias between 2 date range
    By Jul Stev in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-05-2013, 07:08 AM
  7. Advancedfilter copy in place doesn't use criteria range correctly
    By Josh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2005, 10:35 AM

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