=SUMPRODUCT
 

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.

=SUMPRODUCT
 

Hi!

Try this:

=SUMPRODUCT(--(D1:D50000=C6),--(ISNUMBER(MATCH(G1:G50000,{"Closed","In
Progress","Not Started"},0))),--(A1:A50000=AN1),--(A1:A50000<=AO1))

Biff

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

=SUMPRODUCT
 

If you're looking to get individual counts for "Closed", "Not Started", and
"In Progress", just copy your original formula to another cell and change
"Closed" to one of the other values.

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

However, if you are looking for a single count of all items that are either
"Closed", "Not Started", OR "In Progress", then take a look at my response in
your "count if" thread.

Does that help?

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

XL2002, WinXP-Pro


"Jim" wrote:

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.

=SUMPRODUCT
 

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

=SUMPRODUCT
 

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.

=SUMPRODUCT
 

Hey Ron,

Just noticed your leading double unary as well.

If you use the mulitiplier, double unary is not required, even for the firt
condition, as multiplying the first TRUE/FALS arraty by the second forves a
composite 1/0 array.

--
HTH

RP
"Ron Coderre" wrote in message
...
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.

=SUMPRODUCT
 

Them darn asterisks!

Bob


"Ron Coderre" wrote in message
...
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.

=SUMPRODUCT
 

Yeah...I know...it's redundant...but, I just can't break that habit.


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

XL2002, WinXP-Pro


"Bob Phillips" wrote:

Hey Ron,

Just noticed your leading double unary as well.

If you use the mulitiplier, double unary is not required, even for the firt
condition, as multiplying the first TRUE/FALS arraty by the second forves a
composite 1/0 array.

--
HTH

RP
"Ron Coderre" wrote in message
...
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.

=SUMPRODUCT
 

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

If the OP would be willing to put Closed, In Progress and Not Started into
separate adjacent cells, say, X99:Z99, then the formula could be simplified
to

=SUMPRODUCT(--(CS_Ticket_Report_Dump!D$1:D$50000=C6),
COUNTIF(X99:Z99,CS_Ticket_Report_Dump!G$1:G$50000) ,
--(ABS(CS_Ticket_Report_Dump!A$1:A$50000-(AN$1+AO$1)/2)<=(AO$1-AN$1)/2))

=SUMPRODUCT
 

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