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