Count number of name by condition of other column

**him0331** · 02-11-2014, 02:41 AM

Dear all,

This is my first post as I have a big problem in my excel sheet.
I have a sheet that goes on to 1000 rows and let say column A contains 10 to 15 different people names and column B contain different dates, not in order.
Is there a way that could show how many times does a specific name occur in one specific date?

Thank you all!!! Appreciate your help in advance!!

**Kaper** · 02-11-2014, 03:00 AM

The easiest way to obtain real summarry of names and dates with counting is Pivot Table.
Try this tool, once you start using it, your excel productivity will leap 2 levels up

If you want to do it more "manually" (Just for 1 name and specific date or dates range) use COUNTIFS function

**tanvi_kalra** · 02-11-2014, 03:10 AM

If the suggestion helps you, then Click *below to Add Reputation

**him0331** · 02-11-2014, 03:20 AM

Dear Kaper,

Thanks a lot for the suggestion, I haven't use pivot table before so I will give it a try later on. I actually did some research before posting here and I found a lot suggesting pivot table as well. I think I will have to learn it any how :P

**tanvi_kalra** · 02-11-2014, 03:09 AM

Yes you can use Countifs function and give conditions in that Eg
By using
=COUNTIFS($A:$A,"Name",$B:$B,"Date")
Give name and date according to your requirement.

**him0331** · 02-11-2014, 03:22 AM

Thanks tanvi_kalra for your suggestions. I did use countif and countifs before and only this time is gave me #VALUE! instead
I used =COUNTIFS(A2:A100,"Peter",B2:B100,"2/2/2014"), I even tired "*xxxxx*" and still no luck.....

**tanvi_kalra** · 02-11-2014, 03:27 AM

Check once Is the Name and date format is correct??
its better if you can share some sample sheet so that i can identify the error easily.

Thanks ,
Tanvi

**inn** · 02-11-2014, 04:20 AM

I think its best you share a sample worksheet like Tanvi rightly said because this code:

=COUNTIFS(A2:A100,"Peter",B2:B100,"2/2/2014") Should work easy provided the column contains your search items.

try; =COUNTIFS($A:$A,"Peter",$B:$B,"2/2/2014")

**benishiryo** · 02-11-2014, 04:32 AM

welcome to the forum. try not to use dates like 2/2/2014 with the months in numbers. it is both confusing & applicable to only a set of regional settings. i don't know if that is DMY or MDY. so if your region settings is MDY with this data:
Data Range

	A	B
1	Peter	1-Feb-14
2	Bob	1-Feb-14
3	Peter	2-Jan-14
4	Peter	2-Jan-14

and you use:
=COUNTIFS($A:$A,"Peter",$B:$B,"2/1/2014")

because your region settings is MDY, it will return 1 count. mine in DMY will return 2 counts. so use this instead:
=COUNTIFS($A:$A,"Peter",$B:$B,"1Feb2014")

**Kaper** · 02-11-2014, 04:54 AM

Or, to be independant on local setings:

=COUNTIFS($A:$A,"Peter",$B:$B,DATE(2014,2,2))

arguments in inner function DATE are in Y, M, D sequence
The only requirement is that in column B there are real excel dates (not texts, which only look similar to dates).

**him0331** · 02-12-2014, 03:35 AM

Guys, thank you so much. I had this problem for months and I can't believe you guys just solved my problem within hours!!!!!
I used: =COUNTIFS($L:$L,"Peter",$U:$U,"1/1/2014") **Eventho in cell it shows 1-Jan-2014**
You guys are AMAZING!!!

btw Kaper, quick question, I was watching video to learn pivot table as I said before and would that be able to insert the pivot table in the same sheet as the data? every time i pull out the pivot table it brings out a new sheet. and also would the data in pivot table change if I change the primary date in the original column?

sorry for all these stupid questions I asked these two days :P

However, once again a big thank you to you guys!!!!

**Kaper** · 02-12-2014, 09:50 AM

Sure, you can place pivot table in the same sheet your data is.
During Insert->Pivot Table you see dialog window similar to one on picture attached (but in English, while my Excel "speaks" Polish) - just choose option button "place Pivot Table in the current Sheet" and show where to place it (outside raw data).

Refreshing - basically you have to refresh Pivot Table manually.
(Can be perceived positive, because with large datasets formulas tend to take tiiiiime to calculate).