|
count if
Hello,
The help I need is in writing a formula that will count the number of times the store is listed (Column B) when it matches with closed (Column C). On the table listed below I will return the data using a match. From this table A B C 1/8/2006 9:45 Store 1 Closed 1/8/2006 9:57 Store 2 Closed 1/8/2006 10:05 Store 3 Closed 1/8/2006 10:09 Store 4 Closed 1/8/2006 10:15 Store 5 Closed 1/8/2006 10:24 Store 1 Closed 1/8/2006 10:36 Store 2 In Progress 1/8/2006 10:41 Store 3 In Progress 1/8/2006 10:50 Store 4 Closed 1/8/2006 10:58 Store 5 Closed 1/8/2006 10:59 Store 1 Closed 1/8/2006 11:15 Store 2 Closed 1/8/2006 11:22 Store 3 In Progress 1/8/2006 11:24 Store 4 In Progress 1/8/2006 11:33 Store 5 Closed 1/8/2006 11:51 Store 1 Closed 1/8/2006 11:56 Store 2 Closed 1/8/2006 11:57 Store 3 Closed 1/8/2006 12:03 Store 4 Closed 1/8/2006 12:16 Store 5 Not Started 1/8/2006 12:23 Store 1 Closed 1/8/2006 12:28 Store 2 Closed 1/8/2006 12:57 Store 3 Closed To this table A B C 1/8/2006 9:45 Store 1 5 1/8/2006 9:57 Store 2 4 1/8/2006 10:05 Store 3 3 1/8/2006 10:09 Store 4 3 |
count if
Try something like this:
Using your example data in Cells A1:C23 F1: Store 1 G1: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")) Copy that formula down as far as needed Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Hello, The help I need is in writing a formula that will count the number of times the store is listed (Column B) when it matches with closed (Column C). On the table listed below I will return the data using a match. From this table A B C 1/8/2006 9:45 Store 1 Closed 1/8/2006 9:57 Store 2 Closed 1/8/2006 10:05 Store 3 Closed 1/8/2006 10:09 Store 4 Closed 1/8/2006 10:15 Store 5 Closed 1/8/2006 10:24 Store 1 Closed 1/8/2006 10:36 Store 2 In Progress 1/8/2006 10:41 Store 3 In Progress 1/8/2006 10:50 Store 4 Closed 1/8/2006 10:58 Store 5 Closed 1/8/2006 10:59 Store 1 Closed 1/8/2006 11:15 Store 2 Closed 1/8/2006 11:22 Store 3 In Progress 1/8/2006 11:24 Store 4 In Progress 1/8/2006 11:33 Store 5 Closed 1/8/2006 11:51 Store 1 Closed 1/8/2006 11:56 Store 2 Closed 1/8/2006 11:57 Store 3 Closed 1/8/2006 12:03 Store 4 Closed 1/8/2006 12:16 Store 5 Not Started 1/8/2006 12:23 Store 1 Closed 1/8/2006 12:28 Store 2 Closed 1/8/2006 12:57 Store 3 Closed To this table A B C 1/8/2006 9:45 Store 1 5 1/8/2006 9:57 Store 2 4 1/8/2006 10:05 Store 3 3 1/8/2006 10:09 Store 4 3 |
count if
I will assume the first table to be on Sheet 1 in A1:C23
and the second to be on Sheet2 in A1:C4 On Sheet2 in C1 enter =SUMPRODUCT(--(Sheet1!$B$1:$B$23=Sheet2!B1),--(Sheet1!$C$1:$C$23="Closed")) Copy down the column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jim" wrote in message ... Hello, The help I need is in writing a formula that will count the number of times the store is listed (Column B) when it matches with closed (Column C). On the table listed below I will return the data using a match. From this table A B C 1/8/2006 9:45 Store 1 Closed 1/8/2006 9:57 Store 2 Closed 1/8/2006 10:05 Store 3 Closed 1/8/2006 10:09 Store 4 Closed 1/8/2006 10:15 Store 5 Closed 1/8/2006 10:24 Store 1 Closed 1/8/2006 10:36 Store 2 In Progress 1/8/2006 10:41 Store 3 In Progress 1/8/2006 10:50 Store 4 Closed 1/8/2006 10:58 Store 5 Closed 1/8/2006 10:59 Store 1 Closed 1/8/2006 11:15 Store 2 Closed 1/8/2006 11:22 Store 3 In Progress 1/8/2006 11:24 Store 4 In Progress 1/8/2006 11:33 Store 5 Closed 1/8/2006 11:51 Store 1 Closed 1/8/2006 11:56 Store 2 Closed 1/8/2006 11:57 Store 3 Closed 1/8/2006 12:03 Store 4 Closed 1/8/2006 12:16 Store 5 Not Started 1/8/2006 12:23 Store 1 Closed 1/8/2006 12:28 Store 2 Closed 1/8/2006 12:57 Store 3 Closed To this table A B C 1/8/2006 9:45 Store 1 5 1/8/2006 9:57 Store 2 4 1/8/2006 10:05 Store 3 3 1/8/2006 10:09 Store 4 3 |
count if
Perfect!!!
Thanks one more question. How would the sumproduct look if I wanted to match a range of dates from column A. Say the date range is Jan 8, 2006 through Jan 14, 2006. "Bernard Liengme" wrote: I will assume the first table to be on Sheet 1 in A1:C23 and the second to be on Sheet2 in A1:C4 On Sheet2 in C1 enter =SUMPRODUCT(--(Sheet1!$B$1:$B$23=Sheet2!B1),--(Sheet1!$C$1:$C$23="Closed")) Copy down the column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jim" wrote in message ... Hello, The help I need is in writing a formula that will count the number of times the store is listed (Column B) when it matches with closed (Column C). On the table listed below I will return the data using a match. From this table A B C 1/8/2006 9:45 Store 1 Closed 1/8/2006 9:57 Store 2 Closed 1/8/2006 10:05 Store 3 Closed 1/8/2006 10:09 Store 4 Closed 1/8/2006 10:15 Store 5 Closed 1/8/2006 10:24 Store 1 Closed 1/8/2006 10:36 Store 2 In Progress 1/8/2006 10:41 Store 3 In Progress 1/8/2006 10:50 Store 4 Closed 1/8/2006 10:58 Store 5 Closed 1/8/2006 10:59 Store 1 Closed 1/8/2006 11:15 Store 2 Closed 1/8/2006 11:22 Store 3 In Progress 1/8/2006 11:24 Store 4 In Progress 1/8/2006 11:33 Store 5 Closed 1/8/2006 11:51 Store 1 Closed 1/8/2006 11:56 Store 2 Closed 1/8/2006 11:57 Store 3 Closed 1/8/2006 12:03 Store 4 Closed 1/8/2006 12:16 Store 5 Not Started 1/8/2006 12:23 Store 1 Closed 1/8/2006 12:28 Store 2 Closed 1/8/2006 12:57 Store 3 Closed To this table A B C 1/8/2006 9:45 Store 1 5 1/8/2006 9:57 Store 2 4 1/8/2006 10:05 Store 3 3 1/8/2006 10:09 Store 4 3 |
count if
Perfect!!!
Thanks one more question. How would the sumproduct look if I wanted to match a range of dates from column A. Say the date range is Jan 8, 2006 through Jan 14, 2006. "Ron Coderre" wrote: Try something like this: Using your example data in Cells A1:C23 F1: Store 1 G1: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")) Copy that formula down as far as needed Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Hello, The help I need is in writing a formula that will count the number of times the store is listed (Column B) when it matches with closed (Column C). On the table listed below I will return the data using a match. From this table A B C 1/8/2006 9:45 Store 1 Closed 1/8/2006 9:57 Store 2 Closed 1/8/2006 10:05 Store 3 Closed 1/8/2006 10:09 Store 4 Closed 1/8/2006 10:15 Store 5 Closed 1/8/2006 10:24 Store 1 Closed 1/8/2006 10:36 Store 2 In Progress 1/8/2006 10:41 Store 3 In Progress 1/8/2006 10:50 Store 4 Closed 1/8/2006 10:58 Store 5 Closed 1/8/2006 10:59 Store 1 Closed 1/8/2006 11:15 Store 2 Closed 1/8/2006 11:22 Store 3 In Progress 1/8/2006 11:24 Store 4 In Progress 1/8/2006 11:33 Store 5 Closed 1/8/2006 11:51 Store 1 Closed 1/8/2006 11:56 Store 2 Closed 1/8/2006 11:57 Store 3 Closed 1/8/2006 12:03 Store 4 Closed 1/8/2006 12:16 Store 5 Not Started 1/8/2006 12:23 Store 1 Closed 1/8/2006 12:28 Store 2 Closed 1/8/2006 12:57 Store 3 Closed To this table A B C 1/8/2006 9:45 Store 1 5 1/8/2006 9:57 Store 2 4 1/8/2006 10:05 Store 3 3 1/8/2006 10:09 Store 4 3 |
count if
Try this:
Using the same example I posted earlier. I1: StartDate I2: 01/10/2006 J1: EndDate J2: 01/15/2006 new formula for G2: G2: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")*($A$1:$A$23 =$I$2)*($A$1:$A$23<=$J$2)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Perfect!!! Thanks one more question. How would the sumproduct look if I wanted to match a range of dates from column A. Say the date range is Jan 8, 2006 through Jan 14, 2006. "Ron Coderre" wrote: Try something like this: Using your example data in Cells A1:C23 F1: Store 1 G1: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")) Copy that formula down as far as needed Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Hello, The help I need is in writing a formula that will count the number of times the store is listed (Column B) when it matches with closed (Column C). On the table listed below I will return the data using a match. From this table A B C 1/8/2006 9:45 Store 1 Closed 1/8/2006 9:57 Store 2 Closed 1/8/2006 10:05 Store 3 Closed 1/8/2006 10:09 Store 4 Closed 1/8/2006 10:15 Store 5 Closed 1/8/2006 10:24 Store 1 Closed 1/8/2006 10:36 Store 2 In Progress 1/8/2006 10:41 Store 3 In Progress 1/8/2006 10:50 Store 4 Closed 1/8/2006 10:58 Store 5 Closed 1/8/2006 10:59 Store 1 Closed 1/8/2006 11:15 Store 2 Closed 1/8/2006 11:22 Store 3 In Progress 1/8/2006 11:24 Store 4 In Progress 1/8/2006 11:33 Store 5 Closed 1/8/2006 11:51 Store 1 Closed 1/8/2006 11:56 Store 2 Closed 1/8/2006 11:57 Store 3 Closed 1/8/2006 12:03 Store 4 Closed 1/8/2006 12:16 Store 5 Not Started 1/8/2006 12:23 Store 1 Closed 1/8/2006 12:28 Store 2 Closed 1/8/2006 12:57 Store 3 Closed To this table A B C 1/8/2006 9:45 Store 1 5 1/8/2006 9:57 Store 2 4 1/8/2006 10:05 Store 3 3 1/8/2006 10:09 Store 4 3 |
count if
Actually, that new formula should be in G1 :\
New formula for G1: G1: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")*($A$1:$A$23 =$I$2)*($A$1:$A$23<=$J$2)) Sorry for any confusion. *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: Try this: Using the same example I posted earlier. I1: StartDate I2: 01/10/2006 J1: EndDate J2: 01/15/2006 new formula for G2: G2: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")*($A$1:$A$23 =$I$2)*($A$1:$A$23<=$J$2)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Perfect!!! Thanks one more question. How would the sumproduct look if I wanted to match a range of dates from column A. Say the date range is Jan 8, 2006 through Jan 14, 2006. "Ron Coderre" wrote: Try something like this: Using your example data in Cells A1:C23 F1: Store 1 G1: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")) Copy that formula down as far as needed Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Hello, The help I need is in writing a formula that will count the number of times the store is listed (Column B) when it matches with closed (Column C). On the table listed below I will return the data using a match. From this table A B C 1/8/2006 9:45 Store 1 Closed 1/8/2006 9:57 Store 2 Closed 1/8/2006 10:05 Store 3 Closed 1/8/2006 10:09 Store 4 Closed 1/8/2006 10:15 Store 5 Closed 1/8/2006 10:24 Store 1 Closed 1/8/2006 10:36 Store 2 In Progress 1/8/2006 10:41 Store 3 In Progress 1/8/2006 10:50 Store 4 Closed 1/8/2006 10:58 Store 5 Closed 1/8/2006 10:59 Store 1 Closed 1/8/2006 11:15 Store 2 Closed 1/8/2006 11:22 Store 3 In Progress 1/8/2006 11:24 Store 4 In Progress 1/8/2006 11:33 Store 5 Closed 1/8/2006 11:51 Store 1 Closed 1/8/2006 11:56 Store 2 Closed 1/8/2006 11:57 Store 3 Closed 1/8/2006 12:03 Store 4 Closed 1/8/2006 12:16 Store 5 Not Started 1/8/2006 12:23 Store 1 Closed 1/8/2006 12:28 Store 2 Closed 1/8/2006 12:57 Store 3 Closed To this table A B C 1/8/2006 9:45 Store 1 5 1/8/2006 9:57 Store 2 4 1/8/2006 10:05 Store 3 3 1/8/2006 10:09 Store 4 3 |
count if
no confusion here - this is great - thanks!
"Ron Coderre" wrote: Actually, that new formula should be in G1 :\ New formula for G1: G1: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")*($A$1:$A$23 =$I$2)*($A$1:$A$23<=$J$2)) Sorry for any confusion. *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: Try this: Using the same example I posted earlier. I1: StartDate I2: 01/10/2006 J1: EndDate J2: 01/15/2006 new formula for G2: G2: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")*($A$1:$A$23 =$I$2)*($A$1:$A$23<=$J$2)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Perfect!!! Thanks one more question. How would the sumproduct look if I wanted to match a range of dates from column A. Say the date range is Jan 8, 2006 through Jan 14, 2006. "Ron Coderre" wrote: Try something like this: Using your example data in Cells A1:C23 F1: Store 1 G1: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")) Copy that formula down as far as needed Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Hello, The help I need is in writing a formula that will count the number of times the store is listed (Column B) when it matches with closed (Column C). On the table listed below I will return the data using a match. From this table A B C 1/8/2006 9:45 Store 1 Closed 1/8/2006 9:57 Store 2 Closed 1/8/2006 10:05 Store 3 Closed 1/8/2006 10:09 Store 4 Closed 1/8/2006 10:15 Store 5 Closed 1/8/2006 10:24 Store 1 Closed 1/8/2006 10:36 Store 2 In Progress 1/8/2006 10:41 Store 3 In Progress 1/8/2006 10:50 Store 4 Closed 1/8/2006 10:58 Store 5 Closed 1/8/2006 10:59 Store 1 Closed 1/8/2006 11:15 Store 2 Closed 1/8/2006 11:22 Store 3 In Progress 1/8/2006 11:24 Store 4 In Progress 1/8/2006 11:33 Store 5 Closed 1/8/2006 11:51 Store 1 Closed 1/8/2006 11:56 Store 2 Closed 1/8/2006 11:57 Store 3 Closed 1/8/2006 12:03 Store 4 Closed 1/8/2006 12:16 Store 5 Not Started 1/8/2006 12:23 Store 1 Closed 1/8/2006 12:28 Store 2 Closed 1/8/2006 12:57 Store 3 Closed To this table A B C 1/8/2006 9:45 Store 1 5 1/8/2006 9:57 Store 2 4 1/8/2006 10:05 Store 3 3 1/8/2006 10:09 Store 4 3 |
count if
Ron,
I have tried applying the same logic to another formula where I wanted to add Not Stated and In Progress. However when I do I am receiving a 0 number in return. The formula I wrote was: =SUMPRODUCT(--(CS_Ticket_Report_Dump!D$1:D$50000=C6)* (CS_Ticket_Report_Dump!G$1:G$50000="Closed")* (CS_Ticket_Report_Dump!G$1:G$50000="In Progress")* (CS_Ticket_Report_Dump!G$1:G$50000="Not Started")* (CS_Ticket_Report_Dump!A$1:A$50000=AN$1)* (CS_Ticket_Report_Dump!A$1:A$50000<=AO$1)) What am I missing? Thanks for your help. "Ron Coderre" wrote: Actually, that new formula should be in G1 :\ New formula for G1: G1: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")*($A$1:$A$23 =$I$2)*($A$1:$A$23<=$J$2)) Sorry for any confusion. *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: Try this: Using the same example I posted earlier. I1: StartDate I2: 01/10/2006 J1: EndDate J2: 01/15/2006 new formula for G2: G2: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")*($A$1:$A$23 =$I$2)*($A$1:$A$23<=$J$2)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Perfect!!! Thanks one more question. How would the sumproduct look if I wanted to match a range of dates from column A. Say the date range is Jan 8, 2006 through Jan 14, 2006. "Ron Coderre" wrote: Try something like this: Using your example data in Cells A1:C23 F1: Store 1 G1: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")) Copy that formula down as far as needed Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Hello, The help I need is in writing a formula that will count the number of times the store is listed (Column B) when it matches with closed (Column C). On the table listed below I will return the data using a match. From this table A B C 1/8/2006 9:45 Store 1 Closed 1/8/2006 9:57 Store 2 Closed 1/8/2006 10:05 Store 3 Closed 1/8/2006 10:09 Store 4 Closed 1/8/2006 10:15 Store 5 Closed 1/8/2006 10:24 Store 1 Closed 1/8/2006 10:36 Store 2 In Progress 1/8/2006 10:41 Store 3 In Progress 1/8/2006 10:50 Store 4 Closed 1/8/2006 10:58 Store 5 Closed 1/8/2006 10:59 Store 1 Closed 1/8/2006 11:15 Store 2 Closed 1/8/2006 11:22 Store 3 In Progress 1/8/2006 11:24 Store 4 In Progress 1/8/2006 11:33 Store 5 Closed 1/8/2006 11:51 Store 1 Closed 1/8/2006 11:56 Store 2 Closed 1/8/2006 11:57 Store 3 Closed 1/8/2006 12:03 Store 4 Closed 1/8/2006 12:16 Store 5 Not Started 1/8/2006 12:23 Store 1 Closed 1/8/2006 12:28 Store 2 Closed 1/8/2006 12:57 Store 3 Closed To this table A B C 1/8/2006 9:45 Store 1 5 1/8/2006 9:57 Store 2 4 1/8/2006 10:05 Store 3 3 1/8/2006 10:09 Store 4 3 |
count if
Here's what you're missing in this formula:
=SUMPRODUCT(--(CS_Ticket_Report_Dump!D$1:D$50000=C6)* (CS_Ticket_Report_Dump!G$1:G$50000="Closed")* (CS_Ticket_Report_Dump!G$1:G$50000="In Progress")* (CS_Ticket_Report_Dump!G$1:G$50000="Not Started")* (CS_Ticket_Report_Dump!A$1:A$50000=AN$1)* (CS_Ticket_Report_Dump!A$1:A$50000<=AO$1)) "Closed", "In Progress", and "Not Started" are mutually exclusive, yet you are testind if each cell in G1:G50000 is equal to all three. I'd try something like this: =SUMPRODUCT(--(CS_Ticket_Report_Dump!D$1:D$50000=C6)* ISNUMBER(SEARCH(CS_Ticket_Report_Dump!G$1:G$50000, "Closed_In Progress_Not Started"))* (CS_Ticket_Report_Dump!A$1:A$50000=AN$1)* (CS_Ticket_Report_Dump!A$1:A$50000<=AO$1)) Here's the edited part: ISNUMBER(SEARCH(CS_Ticket_Report_Dump!G$1:G$50000, "Closed_In Progress_Not Started")) It checks if it can find the values in G1:G50000 in the string "Closed_In Progress_Not Started". That section includes each of the values you are trying to match separated by a bottomline character. Note: if SEARCH cannot find a match, it returns an error, hence the ISNUMBER function. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Ron, I have tried applying the same logic to another formula where I wanted to add Not Stated and In Progress. However when I do I am receiving a 0 number in return. The formula I wrote was: =SUMPRODUCT(--(CS_Ticket_Report_Dump!D$1:D$50000=C6)* (CS_Ticket_Report_Dump!G$1:G$50000="Closed")* (CS_Ticket_Report_Dump!G$1:G$50000="In Progress")* (CS_Ticket_Report_Dump!G$1:G$50000="Not Started")* (CS_Ticket_Report_Dump!A$1:A$50000=AN$1)* (CS_Ticket_Report_Dump!A$1:A$50000<=AO$1)) What am I missing? Thanks for your help. "Ron Coderre" wrote: Actually, that new formula should be in G1 :\ New formula for G1: G1: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")*($A$1:$A$23 =$I$2)*($A$1:$A$23<=$J$2)) Sorry for any confusion. *********** Regards, Ron XL2002, WinXP-Pro "Ron Coderre" wrote: Try this: Using the same example I posted earlier. I1: StartDate I2: 01/10/2006 J1: EndDate J2: 01/15/2006 new formula for G2: G2: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")*($A$1:$A$23 =$I$2)*($A$1:$A$23<=$J$2)) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Perfect!!! Thanks one more question. How would the sumproduct look if I wanted to match a range of dates from column A. Say the date range is Jan 8, 2006 through Jan 14, 2006. "Ron Coderre" wrote: Try something like this: Using your example data in Cells A1:C23 F1: Store 1 G1: =SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")) Copy that formula down as far as needed Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Jim" wrote: Hello, The help I need is in writing a formula that will count the number of times the store is listed (Column B) when it matches with closed (Column C). On the table listed below I will return the data using a match. From this table A B C 1/8/2006 9:45 Store 1 Closed 1/8/2006 9:57 Store 2 Closed 1/8/2006 10:05 Store 3 Closed 1/8/2006 10:09 Store 4 Closed 1/8/2006 10:15 Store 5 Closed 1/8/2006 10:24 Store 1 Closed 1/8/2006 10:36 Store 2 In Progress 1/8/2006 10:41 Store 3 In Progress 1/8/2006 10:50 Store 4 Closed 1/8/2006 10:58 Store 5 Closed 1/8/2006 10:59 Store 1 Closed 1/8/2006 11:15 Store 2 Closed 1/8/2006 11:22 Store 3 In Progress 1/8/2006 11:24 Store 4 In Progress 1/8/2006 11:33 Store 5 Closed 1/8/2006 11:51 Store 1 Closed 1/8/2006 11:56 Store 2 Closed 1/8/2006 11:57 Store 3 Closed 1/8/2006 12:03 Store 4 Closed 1/8/2006 12:16 Store 5 Not Started 1/8/2006 12:23 Store 1 Closed 1/8/2006 12:28 Store 2 Closed 1/8/2006 12:57 Store 3 Closed To this table A B C 1/8/2006 9:45 Store 1 5 1/8/2006 9:57 Store 2 4 1/8/2006 10:05 Store 3 3 1/8/2006 10:09 Store 4 3 |
count if
=SUMPRODUCT(--(range=Date(year(2006),month(1),day(8)),(--(range<=Date(year(2006),month(1),day(14)),.....)
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jim" wrote in message ... Perfect!!! Thanks - one more question. How would the sumproduct look if I wanted to match a range of dates from column A. Say the date range is Jan 8, 2006 through Jan 14, 2006. "Bernard Liengme" wrote: I will assume the first table to be on Sheet 1 in A1:C23 and the second to be on Sheet2 in A1:C4 On Sheet2 in C1 enter =SUMPRODUCT(--(Sheet1!$B$1:$B$23=Sheet2!B1),--(Sheet1!$C$1:$C$23="Closed")) Copy down the column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jim" wrote in message ... Hello, The help I need is in writing a formula that will count the number of times the store is listed (Column B) when it matches with closed (Column C). On the table listed below I will return the data using a match. From this table A B C 1/8/2006 9:45 Store 1 Closed 1/8/2006 9:57 Store 2 Closed 1/8/2006 10:05 Store 3 Closed 1/8/2006 10:09 Store 4 Closed 1/8/2006 10:15 Store 5 Closed 1/8/2006 10:24 Store 1 Closed 1/8/2006 10:36 Store 2 In Progress 1/8/2006 10:41 Store 3 In Progress 1/8/2006 10:50 Store 4 Closed 1/8/2006 10:58 Store 5 Closed 1/8/2006 10:59 Store 1 Closed 1/8/2006 11:15 Store 2 Closed 1/8/2006 11:22 Store 3 In Progress 1/8/2006 11:24 Store 4 In Progress 1/8/2006 11:33 Store 5 Closed 1/8/2006 11:51 Store 1 Closed 1/8/2006 11:56 Store 2 Closed 1/8/2006 11:57 Store 3 Closed 1/8/2006 12:03 Store 4 Closed 1/8/2006 12:16 Store 5 Not Started 1/8/2006 12:23 Store 1 Closed 1/8/2006 12:28 Store 2 Closed 1/8/2006 12:57 Store 3 Closed To this table A B C 1/8/2006 9:45 Store 1 5 1/8/2006 9:57 Store 2 4 1/8/2006 10:05 Store 3 3 1/8/2006 10:09 Store 4 3 |
count if
I bet Bernard meant:
=SUMPRODUCT(--(range=Date(2006,1,8)),(--(range<=Date(2006,1,14)),.....) Year(2006) = 1905 Bernard Liengme wrote: =SUMPRODUCT(--(range=Date(year(2006),month(1),day(8)),(--(range<=Date(year(2006),month(1),day(14)),.....) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jim" wrote in message ... Perfect!!! Thanks - one more question. How would the sumproduct look if I wanted to match a range of dates from column A. Say the date range is Jan 8, 2006 through Jan 14, 2006. "Bernard Liengme" wrote: I will assume the first table to be on Sheet 1 in A1:C23 and the second to be on Sheet2 in A1:C4 On Sheet2 in C1 enter =SUMPRODUCT(--(Sheet1!$B$1:$B$23=Sheet2!B1),--(Sheet1!$C$1:$C$23="Closed")) Copy down the column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jim" wrote in message ... Hello, The help I need is in writing a formula that will count the number of times the store is listed (Column B) when it matches with closed (Column C). On the table listed below I will return the data using a match. From this table A B C 1/8/2006 9:45 Store 1 Closed 1/8/2006 9:57 Store 2 Closed 1/8/2006 10:05 Store 3 Closed 1/8/2006 10:09 Store 4 Closed 1/8/2006 10:15 Store 5 Closed 1/8/2006 10:24 Store 1 Closed 1/8/2006 10:36 Store 2 In Progress 1/8/2006 10:41 Store 3 In Progress 1/8/2006 10:50 Store 4 Closed 1/8/2006 10:58 Store 5 Closed 1/8/2006 10:59 Store 1 Closed 1/8/2006 11:15 Store 2 Closed 1/8/2006 11:22 Store 3 In Progress 1/8/2006 11:24 Store 4 In Progress 1/8/2006 11:33 Store 5 Closed 1/8/2006 11:51 Store 1 Closed 1/8/2006 11:56 Store 2 Closed 1/8/2006 11:57 Store 3 Closed 1/8/2006 12:03 Store 4 Closed 1/8/2006 12:16 Store 5 Not Started 1/8/2006 12:23 Store 1 Closed 1/8/2006 12:28 Store 2 Closed 1/8/2006 12:57 Store 3 Closed To this table A B C 1/8/2006 9:45 Store 1 5 1/8/2006 9:57 Store 2 4 1/8/2006 10:05 Store 3 3 1/8/2006 10:09 Store 4 3 -- Dave Peterson |
| All times are GMT +1. The time now is 03:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com