+ Reply to Thread
Results 1 to 6 of 6

More efficient COUNTIF?

  1. 01-20-2022, 11:27 AM #1
    WowImMatt
    WowImMatt is offline
    Registered User
    Join Date
    12-02-2021
    Location
    Cardiff, Wales
    MS-Off Ver
    O365
    Posts
    6

    More efficient COUNTIF?

    Hi folks,

    Wondering if there is a more efficient way of writing the following: 

    Please Login or Register  to view this content.
    (there is meant to be several asperand's (AT) in this, however it wouldn't let me post with these)

    As it stands, this is searching for "x" in multiple columns headed by a week number, however it has to avoid certain other columns which aren't headed by a week number.

    I've attached a snip from the spreadsheet for illustration

    This formula would be repeated for around 1500 rows.

    Thanks,
    Matt
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-20-2022, 11:44 AM #2
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: More efficient COUNTIF?

    How about
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  3. 01-20-2022, 11:45 AM #3
    AliGW
    AliGW is offline
    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,830

    Re: More efficient COUNTIF?

    Yes, indeed. Try this:

    =COUNTIFS(Table6[[#Headers],[Week 1]:[Date of Closures sign off]],"Week"&"*",Table6[@[Week 1]:[Date of Closures sign off]],"x")+1
    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
  4. 01-20-2022, 11:51 AM #4
    WideBoyDixon
    WideBoyDixon is offline
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: More efficient COUNTIF?

    I'm going to assume that you want to count an "x" value in any column that starts with the word "Week". Without structured references you could use in C3:

    Please Login or Register  to view this content.
    Similar approach with structured references but since you can't have a cyclic reference, you'll need to use OFFSET ...

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for ✶ rep!
    Reply With QuoteRegister To Reply
  5. 01-21-2022, 05:26 AM #5
    WowImMatt
    WowImMatt is offline
    Registered User
    Join Date
    12-02-2021
    Location
    Cardiff, Wales
    MS-Off Ver
    O365
    Posts
    6

    Re: More efficient COUNTIF?

    Thanks all, i'll give these suggestions a whirl
    Reply With QuoteRegister To Reply
  6. 01-21-2022, 05:31 AM #6
    AliGW
    AliGW is offline
    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,830

    Re: More efficient COUNTIF?

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

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
    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. VBA more efficient
    By benoj2005 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-27-2021, 12:40 AM
  2. [SOLVED] Efficient Programming
    By GavinMcL in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-27-2014, 12:23 PM
  3. Matching string in an array of strings
    By vientito in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-28-2013, 05:54 PM
  4. Need a more efficient way to use VBA to web scrape
    By Swannekin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-08-2013, 05:47 PM
  5. [SOLVED] more efficient VBA?
    By markx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-07-2006, 12:45 PM
  6. Is there a more efficient way to do this?
    By Steve Roberts in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2005, 01:05 PM
  7. VBA - Efficient Macro
    By ajocius in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-03-2005, 07:34 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1