Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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)) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=SUMPRODUCT formula help | Excel Worksheet Functions | |||
sumproduct between 2 ranges | Excel Discussion (Misc queries) | |||
Sumif Linking to Another Workbook error #VALUE! | Excel Discussion (Misc queries) | |||
=SUMPRODUCT and =IF | Excel Worksheet Functions |