Counting column with text against specified dates in Excel

0
votes

I need to count the text in column B against the column A which consist of dates. For eg. If on 09/25/2019 the column consist of three names "Sukrut. Anand, Sam" but Anand is mentioned on thrice in rows against same date while Sam is twice and sukrut is once.

I exactly want to know the count of names occured on specific dates. Like for 09/25/2019 the names occured for how many times and so on.

I think this better should work with =Countifs or =Sumproduct

Date        Agent Name    Lead details       QA Status (Internal)    QA status (External)
5/9/2019    sukrut        [email protected]    Yes                     Yes
5/9/2019    sukrut        [email protected]    Yes                     No
5/9/2019    sam           [email protected]    No                      No
5/9/2019    sam           [email protected]    No                      No
5/9/2019    anand         [email protected]    No                      No
5/9/2019    sager         [email protected]    Yes                     Yes
5/10/2019   Sukrut        [email protected]    No                      No
5/11/2019   Sukrut        [email protected]    Yes                     No

This is what I tried using =COUNTIF('6AEE97'!$A$3:$A$17,"5/9/2019") - COUNTIF('6AEE97'!$B$3:$B$17,"Sukrut")

1
excelcountifsumproduct
You're correct, Countifs sounds like the way to go. - tigeravatar
I tried using =Countifs but not getting the exact value as required need to understand the "range_criteria" - Мя Рэяғэст
You're going to need to provide sample data, the formula you tried, and the correct expected results. - tigeravatar
No, edit your question to include the sample data. Doesn't have to be real data, just need dummy data that's representative of what you're trying to accomplish. - tigeravatar
can you please refer it now. - Мя Рэяғэст

1 Answers

0
votes

I think a simple pivot table is all you need. Select your table and then Insert -> Pivot Table. Setup your pivot table like so:

Pivot Table Setup

Then just remove grand totals (unless you want to keep them) and I also center-aligned the data to make it easier to review. Here's the result:

Pivot Table Result