count if

[Guest]

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

 

[Guest]

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

 

[Bernard Liengme]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

no confusion here - this is great - thanks!

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

 

[Guest]

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$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.

 

[Guest]

Here's what you're missing in this formula:

=SUMPRODUCT(--(CS_Ticket_Report_Dump!D$1$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$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

 

[Bernard Liengme]

=SUMPRODUCT(--(range>=Date(year(2006),month(1),day(8)),(--(range<=Date(year(2006),month(1),day(14)),.....)

 

[Dave Peterson]

I bet Bernard meant:

=SUMPRODUCT(--(range>=Date(2006,1,8)),(--(range<=Date(2006,1,14)),.....)

Year(2006) = 1905