Home |
Search |
Today's Posts |
#1
|
|||
|
|||
if meet critertia then countif(a1:a10, between dates)
I hv a table of info
Col A = location Col B = Due Dates if meet location, then count the number of due dates that fall in Oct or nov etc.... =SUMPRODUCT(--(A1:A10="location"),--(COUNTIF(b1:b10,DATEDIF("2005/10/1","2005/10/31","yd")))) Col A Col B Location Due Dates USA 10/10/05 Canada 5/1/06 UK 31/12/05 Canada 2/1/06 S. America 30/10/05 Result: oct 05 nov 05 dec 05 jan 06 canada 0 0 0 2 S. America 2 0 0 0 |
#2
|
|||
|
|||
Assuming source table is in A2:B10, and
this results part below is set-up in D1:H3 (E1:H1 contains "1st of month" dates, viz.:1-Oct-2005, 1-Nov-2005 filled across) Result: oct 05 nov 05 dec 05 jan 06 canada 0 0 0 2 S. America 1* 0 0 0 *corrected typo Put in E2: =SUMPRODUCT(($A$2:$A$10=$D2)* ($B$2:$B$10=DATE(YEAR(E$1),MONTH(E$1),1))* (($B$2:$B$10<DATE(YEAR(E$1),MONTH(E$1)+1,1)))) Copy E2 across to H2, fill down to populate the table and return the desired results Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Kikkoman" wrote in message ... I hv a table of info Col A = location Col B = Due Dates if meet location, then count the number of due dates that fall in Oct or nov etc.... =SUMPRODUCT(--(A1:A10="location"),--(COUNTIF(b1:b10,DATEDIF("2005/10/1","200 5/10/31","yd")))) Col A Col B Location Due Dates USA 10/10/05 Canada 5/1/06 UK 31/12/05 Canada 2/1/06 S. America 30/10/05 Result: oct 05 nov 05 dec 05 jan 06 canada 0 0 0 2 S. America 2 0 0 0 |
#3
|
|||
|
|||
Thx Max, it worked great.
(Trying to work out where I went wrong!) "Max" wrote: Assuming source table is in A2:B10, and this results part below is set-up in D1:H3 (E1:H1 contains "1st of month" dates, viz.:1-Oct-2005, 1-Nov-2005 filled across) Result: oct 05 nov 05 dec 05 jan 06 canada 0 0 0 2 S. America 1* 0 0 0 *corrected typo Put in E2: =SUMPRODUCT(($A$2:$A$10=$D2)* ($B$2:$B$10=DATE(YEAR(E$1),MONTH(E$1),1))* (($B$2:$B$10<DATE(YEAR(E$1),MONTH(E$1)+1,1)))) Copy E2 across to H2, fill down to populate the table and return the desired results Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Kikkoman" wrote in message ... I hv a table of info Col A = location Col B = Due Dates if meet location, then count the number of due dates that fall in Oct or nov etc.... =SUMPRODUCT(--(A1:A10="location"),--(COUNTIF(b1:b10,DATEDIF("2005/10/1","200 5/10/31","yd")))) Col A Col B Location Due Dates USA 10/10/05 Canada 5/1/06 UK 31/12/05 Canada 2/1/06 S. America 30/10/05 Result: oct 05 nov 05 dec 05 jan 06 canada 0 0 0 2 S. America 2 0 0 0 |
#4
|
|||
|
|||
Glad it helped !
... and just realized there was an extra, unnecessary pair of parens (around the 3rd cond) in the earlier formula, sorry. Put instead in E2, and fill across and down: =SUMPRODUCT(($A$2:$A$10=$D2)* ($B$2:$B$10=DATE(YEAR(E$1),MONTH(E$1),1))* ($B$2:$B$10<DATE(YEAR(E$1),MONTH(E$1)+1,1))) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Kikkoman" wrote in message ... Thx Max, it worked great. (Trying to work out where I went wrong!) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting cells that meet 2 differnet criteria | Excel Worksheet Functions | |||
count non blank cells which meet criteria in another column | Excel Worksheet Functions | |||
Sum the values of one column, only if they meet certain criteria . | Excel Worksheet Functions | |||
How can I count cells that meet two criteria within a filtered co. | Excel Worksheet Functions | |||
Deleting/IDing Rows that Don't Meet Criteria | Excel Worksheet Functions |