+ Reply to Thread
Results 1 to 7 of 7

COUNTIF while a condition is met

  1. #1
    Registered User
    Join Date
    04-26-2014
    Location
    maryland
    MS-Off Ver
    excel 2013
    Posts
    8

    COUNTIF while a condition is met

    Hi all,

    I swear I've seen a way to do this before but it escapes me and I'm just not figuring it out, so hopefully someone can point me in the right direction.

    Ultimately I would like a =while(a:a,month=3,countif(b:b,criteria))

    I know this doesn't exist but something like that

    I'm making a spreadsheet for visitor logs and I want to be able to run some metrics for it
    Column A is the date, B is name, C is time in, D is time out, and E is office visited.

    I want to be able to have a formula for example that would count how many people visited a particular office in February, or count how many people signed in during March. My issue seems to be counting while the date equals a certain month. I've been able to do it with a helper column where I have it do a =month(a2) and then do a =countsifs(helpercolumn, 1(for example jan), e:e, 102) and it will tell me how many people visited office 102 in January but I was hoping to do a formula without the extra column. I'm just now putting this together so I don't have a spreadsheet for it yet but can make a sample data mock up if needed.

    Thanks for your help in advance.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: COUNTIF while a condition is met

    Hi sugar824,

    Look at using a pivot table. By using a pivot table you can build all sorts of metrics in one go until you see what you want.

    http://www.excel-easy.com/data-analy...ot-tables.html

    If you still need a formula solution, you should provide sample data. Not a lot of people are going to generate sample data to hopefully get your structure and more importantly, what you expect as an answer.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    04-26-2014
    Location
    maryland
    MS-Off Ver
    excel 2013
    Posts
    8

    Re: COUNTIF while a condition is met

    Jeff,

    I considered a pivot but I want to be able to just have a quick snap shot without having to manipulate the data. Also, if I can get a formula for it, I want to be able to expand out the log and metrics in the future and be able to modify the formula for use there.

    Also, I'm attaching a sample workbook that shows a basic idea of what I'm going for.
    Attached Files Attached Files

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: COUNTIF while a condition is met

    Hi sugar824,

    How about this...

    Notice how I changed the months to actual dates.
    Attached Files Attached Files

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: COUNTIF while a condition is met

    As an option, if you want to use the entire range reference, you could use...

    =COUNTIFS(Log!$A:$A,">="&Metrics!$B18,Log!$A:$A,"<="&EOMONTH(Metrics!$B18,0),Log!$E:$E,Metrics!C$16)

    If you can't get that to work I can update the workbook.

  6. #6
    Registered User
    Join Date
    04-26-2014
    Location
    maryland
    MS-Off Ver
    excel 2013
    Posts
    8

    Re: COUNTIF while a condition is met

    I think that might work. I haven't used SUMPRODUCT before. What do the -- do if you don't mind my asking. I also didn't realize I could use a range with MONTH which is part of the reason I was getting hung up. I'm going to play around with it this weekend and see if I can modify it for all of the things I need but from what I can tell of the formula it should work for all of it.

    Thank you so much for that and for such a quick response.

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: COUNTIF while a condition is met

    You're very welcome. Happy to help.

    It's called a double unary. A double unary converts TRUE/FALSE into 1/0

    This tutorial should help which also shows other ways to use the SUMPRODUCT function.

    The Sumifs/Countifs and such have somewhat taken the place of the SUMPRODUCT, but not in all cases.

    Happy hunting...

+ 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. How to use CountIF() with a condition?
    By dlowrey in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-09-2013, 02:18 PM
  2. Excel 2007 : Countif with AND condition
    By mklunenberg in forum Excel General
    Replies: 1
    Last Post: 02-13-2012, 12:02 PM
  3. Excel 2007 : Countif with an or condition
    By matt1234 in forum Excel General
    Replies: 4
    Last Post: 05-29-2011, 01:54 AM
  4. Countif - add another condition
    By Excel Beginner99 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 09-14-2010, 11:16 AM
  5. Countif for more than one condition
    By karan in forum Excel General
    Replies: 4
    Last Post: 09-18-2009, 08:11 AM
  6. Countif condition a or condition b....
    By solnajeff in forum Excel General
    Replies: 1
    Last Post: 08-23-2009, 12:32 AM
  7. countif condition
    By xtrmhyper in forum Excel General
    Replies: 3
    Last Post: 05-31-2005, 11:05 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