Count the number of times in particular date a text appear in adjacent column in Microsoft Excel

--------------------------------------------
Dates in column A 		  Text in Column B
--------------------------------------------
03/10/2003		|	AAA
03/15/2003		|	BBB
03/20/2003		|	CCC
03/25/2003		|	AAA
03/30/2003		|	BBB
04/04/2003		|	CCC
03/25/2003		|	AAA
03/30/2003		|	BBB
04/04/2003		|	CCC
03/25/2003		|	AAA
03/30/2003		|	BBB
04/04/2003		|	CCC

Question:
Count how many times the text "AAA" appears next to 03/25/2003?

Answer: 3 times.

Date in cell D1: 	03/25/2003
Text in cell D2: 	AAA

The Formula: 	=SUMPRODUCT ((A1:A12=D1)*(B1:B12=D2))

Screenshot // Count the number of times in particular date a text appear in adjacent column in Microsoft Excel
Count the number of times in particular date a text appear in adjacent column in Microsoft Excel

Users are saying about us...

  1. i have 2 cols (one is date type one is number) like : a1=01/04/2003,a2=02/04/2003...,a20=15/07/2003 and b1=500,b2=450 .How can i sum the b col only to an exact date (i mean this date i introduce in another cell for ex. d1=15/04/2003 and to sum col b only to 15/04/2003).Thanks!

  2. "Hi Leroiv,
    If you have data as follows in A1:B20:
    1 Apr 2003 816
    3 Apr 2003 932
    5 Apr 2003 61
    9 Apr 2003 228
    10 Apr 2003 428
    13 Apr 2003 569
    14 Apr 2003 403
    15 Apr 2003 289
    16 Apr 2003 93
    18 Apr 2003 172
    22 Apr 2003 837
    23 Apr 2003 475
    27 Apr 2003 642
    29 Apr 2003 108
    4 May 2003 73
    7 May 2003 706
    7 May 2003 209
    9 May 2003 413
    13 May 2003 3
    16 May 2003 446 And if D1 contains the date value for, say, 15 April 2003, then the following formula will sum the values in B1:B20 beside the dates that are up to and including 15 April 2003:

    {=SUM(((A1:A20)<=D1)*(B1:B20))}

    Notes:

    1) This is an array formula, so enter it without the braces using Shift-Ctrl-Enter. Read the help for more on arrays.

    2) This is fine as long as your dates in column A, and in D1, are 'clean' and do not include time fractions too (in other words they are integer date values).

    Hope that helps,

    Alan"

  3. i have 2 cols (one is date type one is number) like : a1=01/04/2003,a2=02/04/2003...,a20=15/07/2003 and b1=500,b2=450 .How can i sum the b col only to an exact date (i mean this date i introduce in another cell for ex. d1=15/04/2003 and to sum col b only to 15/04/2003).Thanks!

  4. "Hi Leroiv,

    If you have data as follows in A1:B20:

    1 Apr 2003 816
    3 Apr 2003 932
    5 Apr 2003 61
    9 Apr 2003 228
    10 Apr 2003 428
    13 Apr 2003 569
    14 Apr 2003 403
    15 Apr 2003 289
    16 Apr 2003 93
    18 Apr 2003 172
    22 Apr 2003 837
    23 Apr 2003 475
    27 Apr 2003 642
    29 Apr 2003 108
    4 May 2003 73
    7 May 2003 706
    7 May 2003 209
    9 May 2003 413
    13 May 2003 3
    16 May 2003 446

    And if D1 contains the date value for, say, 15 April 2003, then the following formula will sum the values in B1:B20 beside the dates that are up to and including 15 April 2003:

    {=SUM(((A1:A20)<=D1)*(B1:B20))}

    Notes:

    1) This is an array formula, so enter it without the braces using Shift-Ctrl-Enter. Read the help for more on arrays.

    2) This is fine as long as your dates in column A, and in D1, are 'clean' and do not include time fractions too (in other words they are integer date values).

    Hope that helps,

    Alan"

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube