+ Reply to Thread
Results 1 to 9 of 9

Count number of rows in a range that contain less than 5 cells with the string "VAC"

  1. 08-25-2014, 10:33 AM #1
    bananius
    bananius is offline
    Registered User
    Join Date
    07-29-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    16

    Count number of rows in a range that contain less than 5 cells with the string "VAC"

    Hello,

    I was wondering if anyone could help me create a function that counts only the number of rows in a range which contain less than 5 instances of the string "VAC". So if they have less than 5 cells in the row that contain "VAC" count + 1.

    Any help would be appreciated! Thank you in advance!
    Reply With QuoteRegister To Reply
  2. 08-25-2014, 11:02 AM #2
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Count number of rows in a range that contain less than 5 cells with the string "VAC"

    I'm just guessing based on the info you gave.
    Let's say the range is Columns B:H
    In J2 copied down for example

    =IF(COUNTIF($B2:$H2,"Vac")<5, MAX($J$1:$J1)+1,"")
    Is that what you are looking for?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 08-25-2014, 11:44 AM #3
    bananius
    bananius is offline
    Registered User
    Join Date
    07-29-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    16

    Re: Count number of rows in a range that contain less than 5 cells with the string "VAC"

    Thank you for help ChemistB!

    I definitely did not explain myself well, sorry about that. So the rage of rows in my case goes from AB1:AJ:60. Each row is attached to a person. In this range I need the total count of all the rows that have less than 5 cells that say "VAC" in them. So, out of 60 rows (people), how many have less than 5 cells containing the "VAC" string.

    I wanted to create a loop, but I'm having trouble with the formula... The answer above does help me a bit though so thank you again.
    Reply With QuoteRegister To Reply
  4. 08-25-2014, 12:25 PM #4
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count number of rows in a range that contain less than 5 cells with the string "VAC"

    Hi,

    Perhaps you mean:

    =SUMPRODUCT(0+(COUNTIF(OFFSET(AB1:AJ1,ROW(AB1:AJ60)-MIN(ROW(AB1:AJ60)),,),"VAC")<5))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  5. 08-25-2014, 01:35 PM #5
    bananius
    bananius is offline
    Registered User
    Join Date
    07-29-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    16

    Re: Count number of rows in a range that contain less than 5 cells with the string "VAC"

    Quote Originally Posted by XOR LX View Post
    Hi,

    Perhaps you mean:

    =SUMPRODUCT(0+(COUNTIF(OFFSET(AB1:AJ1,ROW(AB1:AJ60)-MIN(ROW(AB1:AJ60)),,),"VAC")<5))

    Regards
    That worked perfectly! Thank you so much!
    Reply With QuoteRegister To Reply
  6. 08-25-2014, 02:01 PM #6
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count number of rows in a range that contain less than 5 cells with the string "VAC"

    You're welcome!
    Reply With QuoteRegister To Reply
  7. 08-25-2014, 03:38 PM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count number of rows in a range that contain less than 5 cells with the string "VAC"

    Here's another one...

    =SUMPRODUCT(--(MMULT(--(AB1:AJ60="Vac"),{1;1;1;1;1;1;1;1;1})<5))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  8. 08-25-2014, 03:50 PM #8
    bananius
    bananius is offline
    Registered User
    Join Date
    07-29-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    16

    Re: Count number of rows in a range that contain less than 5 cells with the string "VAC"

    Quote Originally Posted by Tony Valko View Post
    Here's another one...

    =SUMPRODUCT(--(MMULT(--(AB1:AJ60="Vac"),{1;1;1;1;1;1;1;1;1})<5))
    Thank you Tony Valko! This works great as well
    Reply With QuoteRegister To Reply
  9. 08-25-2014, 03:58 PM #9
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count number of rows in a range that contain less than 5 cells with the string "VAC"

    You're welcome. Thanks for the feedback!
    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. [SOLVED] VBA "& Cells(Rows.Count)" problem - copying outside of range
    By Dgp2012 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-26-2014, 05:18 AM
  2. range("e1" & cells(rows.Count,5).end(xlup)).Select
    By sohaila in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-09-2014, 09:27 AM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. [SOLVED] Count the number of cells over 35 days excluding rows/cells that have a "yes" value
    By northcoms in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-02-2013, 10:25 AM
  5. Count Number of Rows with cells that contain "Yes".
    By climbjm in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-22-2007, 01:59 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1