+ Reply to Thread
Results 1 to 4 of 4

COUNTIFS and SUMIFS not working on set Ranges

  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,181

    COUNTIFS and SUMIFS not working on set Ranges

    As shown in Cols I - M on SUMMARY sheet, Code should count how many orders a client has placed across three companies for what products, and show the combined volumes and values.

    Have set Named Ranges, but the code calling COUNT IF and SUM IF are not working, and can't see why?


    Please Login or Register  to view this content.
    All solutions, suggestions and alternatives welcome as ever

    Ochimus
    Attached Files Attached Files

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,933

    Re: COUNTIFS and SUMIFS not working on set Ranges

    It's Application.WorksheetFunction not Application.Function

    Sticking On Error Resume Next at the start of a routine and leaving it active the whole time is not good practice.
    Rory
    I drink, and I know things

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,529

    Re: COUNTIFS and SUMIFS not working on set Ranges

    Hi there,

    As Rory says, you should NEVER use "On Error Resume Next" just because your code doesn't work! It is a very useful tool to have available, BUT you should use it only in a very controlled way, and only when you know exactly what you are doing.

    If you had NOT included it, the highlighted errors would have been identified when your code ran. The fact that the errors were ignored has possibly contributed to the incorrect calculations.

    Please Login or Register  to view this content.

    Hope this helps,

    Regards,

    Greg M

  4. #4
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,181

    Re: COUNTIFS and SUMIFS not working on set Ranges

    Many thanks for prompt feedback and pointers, which I put into the Code.

    When it still didn't work, I realised the key error was the Client Name is added to the first row of each block, but the code was looking for it on the last row.

    Added a seperate variable (w) for the Name row, and everything now works perfectly.

    Please Login or Register  to view this content.
    Now marking this as closed

    Ochimus
    Last edited by Ochimus; 10-05-2020 at 10:41 AM.

+ 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. Countifs / Sumifs with dynamic arrays (spilled ranges)
    By esbencito in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-08-2020, 02:43 PM
  2. SUMIFS, COUNTIFS, & SUMPRODUCT - With multiple cell ranges & criteria
    By Iamcourtdz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-18-2020, 11:19 AM
  3. Performance of SUMIFS, COUNTIFs etc with large ranges
    By TheRobsterUK in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-15-2017, 06:19 AM
  4. SUMIFS/COUNTIFS for multiple conditions/ranges
    By BoudeyCall in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-31-2017, 04:35 PM
  5. SUMIFS/COUNTIFS for multiple conditions/ranges
    By BoudeyCall in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-09-2017, 09:11 PM
  6. Countifs and Sumifs not working...y?
    By biznez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2016, 06:10 AM
  7. [SOLVED] countifs working on extended ranges and different sheets not working
    By etaf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2013, 02:23 PM

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