=SUMPRODUCT

J

Jim

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

Biff

Hi!

Try this:

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

Biff
 
R

Ron Coderre

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
 
B

Bob Phillips

=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))
 
R

Ron Coderre

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
 
B

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
Ron Coderre said:
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
 
B

Bob Phillips

Them darn asterisks!

Bob


Ron Coderre said:
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
 
R

Ron Coderre

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


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

XL2002, WinXP-Pro
 
H

Harlan Grove

Bob Phillips said:
=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))
 
A

Aladin Akyurek

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

or

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

are more efficient conditionals than

--(Range={...})

or

--(Range=ConditionList)


Ron said:
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


:

--

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top