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

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

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

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

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

4. 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!

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.