# COUNTIF while a condition is met

1. ## 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.

2. ## 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.

3. ## 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.

4. ## Re: COUNTIF while a condition is met

Hi sugar824,

Notice how I changed the months to actual dates.

5. ## 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. ## 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. ## 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...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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