=SUMPRODUCT

[Guest]

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

 

[Biff]

Hi!

Try this:

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

Biff

 

[Guest]

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

 

[Bob Phillips]

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

 

[Guest]

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

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

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$50000=C6)*(CS_Ticket_Report_Dump!
G$1:G$50000={"Closed","In Progress,"Not

 

[Bob Phillips]

Them darn asterisks!

Bob

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$50000=C6)*(CS_Ticket_Report_Dump!
G$1:G$50000={"Closed","In Progress,"Not

 

[Guest]

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


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

XL2002, WinXP-Pro

 

[Harlan Grove]

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

 

[Aladin Akyurek]

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

or

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

are more efficient conditionals than

--(Range={...})

or

--(Range=ConditionList)

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


:

--

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