--ISNUMBER(MATCH(Range,{...},0))

or

--ISNUMBER(MATCH(Range,ConditionList,0))

are more efficient conditionals than

--(Range={...})

or

--(Range=ConditionList)


Ron Coderre wrote:
Hey, Bob

Absent the couple typos that snuck in there...I like your approach!

Here's what I did with it:
=SUMPRODUCT(--(CS_Ticket_Report_Dump!D$1:D$50000=C6)*(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))


***********
Best Regards,
Ron

XL2002, WinXP-Pro


"Bob Phillips" wrote:


=SUMPRODUCT((--CS_Ticket_Report_Dump!D$1:D$50000=C6)*
--(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))


--
HTH

RP
"Jim" wrote in message
...

I am using this formula to count the number of times "closed" appears

between

particular dates:



=SUMPRODUCT(--($B$1:$B$23=F1)*($C$1:$C$23="Closed")*($A$1:$A$23 =$I$2)*($A$1
:$A$23<=$J$2))

I have tried applying the same logic to another formula where I wanted to
Also count the number of times "Not Stated" and "In Progress" are shown.
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.





--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.