| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SumProduct Question
I have this formula that count the number of times "Barbie Doll" occurs:
=SUMPRODUCT((ISNUMBER(SEARCH("Barbie Doll",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))) How can I change the formula to count everything else OTHER THAN these items: Barbie Doll, Beanie Babies, Toy Cars? Please help. Thank you. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
Are there any empty/blank cells in the range?
Biff "mldancing" wrote in message ... I have this formula that count the number of times "Barbie Doll" occurs: =SUMPRODUCT((ISNUMBER(SEARCH("Barbie Doll",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))) How can I change the formula to count everything else OTHER THAN these items: Barbie Doll, Beanie Babies, Toy Cars? Please help. Thank you. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
There shouldn't be any. But if there is something that says "cancel", then
the formula shouldn't count it. Thank you. "T. Valko" wrote: Are there any empty/blank cells in the range? Biff "mldancing" wrote in message ... I have this formula that count the number of times "Barbie Doll" occurs: =SUMPRODUCT((ISNUMBER(SEARCH("Barbie Doll",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))) How can I change the formula to count everything else OTHER THAN these items: Barbie Doll, Beanie Babies, Toy Cars? Please help. Thank you. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
Let me see if I got this straight.....
You want to count the number of cells in a range that *do not* contain any of the following: Barbie Doll Beanie Babies Toy Cars cancel Make a list of those strings in a range of cells: A91 = Barbie Doll A92 = Beanie Babies A93 = Toy Cars A94 = cancel Then: =SUMPRODUCT(--(ISERROR(SEARCH(A91:A94,C91:C103)))) Biff I'm assuming that since you're using SEARCH in your formula these are substings. "mldancing" wrote in message ... There shouldn't be any. But if there is something that says "cancel", then the formula shouldn't count it. Thank you. "T. Valko" wrote: Are there any empty/blank cells in the range? Biff "mldancing" wrote in message ... I have this formula that count the number of times "Barbie Doll" occurs: =SUMPRODUCT((ISNUMBER(SEARCH("Barbie Doll",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))) How can I change the formula to count everything else OTHER THAN these items: Barbie Doll, Beanie Babies, Toy Cars? Please help. Thank you. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
A bit longish, but think this returns what you're after:
=COUNTIF(C$91:C$103,"<")-SUMPRODUCT((ISNUMBER(SEARCH("Barbie Doll",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))+(ISNUMBER(SEARCH("Beanie Babies",C$91:C$103))*ISERROR(SEARCH("cancel",C$91: C$103)))+(ISNUMBER(SEARCH("Toy Cars",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mldancing" wrote: There shouldn't be any. But if there is something that says "cancel", then the formula shouldn't count it. Thank you. "T. Valko" wrote: Are there any empty/blank cells in the range? Biff "mldancing" wrote in message ... I have this formula that count the number of times "Barbie Doll" occurs: =SUMPRODUCT((ISNUMBER(SEARCH("Barbie Doll",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))) How can I change the formula to count everything else OTHER THAN these items: Barbie Doll, Beanie Babies, Toy Cars? Please help. Thank you. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
A bit longish, but think this returns what you're after:
=COUNTIF(C$91:C$103,"<")-SUMPRODUCT((ISNUMBER(SEARCH("Barbie Doll",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))+(ISNUMBER(SEARCH("Beanie Babies",C$91:C$103))*ISERROR(SEARCH("cancel",C$91: C$103)))+(ISNUMBER(SEARCH("Toy Cars",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mldancing" wrote: There shouldn't be any. But if there is something that says "cancel", then the formula shouldn't count it. Thank you. "T. Valko" wrote: Are there any empty/blank cells in the range? Biff "mldancing" wrote in message ... I have this formula that count the number of times "Barbie Doll" occurs: =SUMPRODUCT((ISNUMBER(SEARCH("Barbie Doll",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))) How can I change the formula to count everything else OTHER THAN these items: Barbie Doll, Beanie Babies, Toy Cars? Please help. Thank you. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
Example:
Barbie Doll Beanie Babies Boats - Cancel Stuffed Toys - Cancel Teddy Bears So the answer should just be 1 (count for 1 occurance of Teddy Bear only). Since I don't want to count Barbie Doll, Beanie Babies, and anything that has the word "Cancel". "T. Valko" wrote: Let me see if I got this straight..... You want to count the number of cells in a range that *do not* contain any of the following: Barbie Doll Beanie Babies Toy Cars cancel Make a list of those strings in a range of cells: A91 = Barbie Doll A92 = Beanie Babies A93 = Toy Cars A94 = cancel Then: =SUMPRODUCT(--(ISERROR(SEARCH(A91:A94,C91:C103)))) Biff I'm assuming that since you're using SEARCH in your formula these are substings. "mldancing" wrote in message ... There shouldn't be any. But if there is something that says "cancel", then the formula shouldn't count it. Thank you. "T. Valko" wrote: Are there any empty/blank cells in the range? Biff "mldancing" wrote in message ... I have this formula that count the number of times "Barbie Doll" occurs: =SUMPRODUCT((ISNUMBER(SEARCH("Barbie Doll",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))) How can I change the formula to count everything else OTHER THAN these items: Barbie Doll, Beanie Babies, Toy Cars? Please help. Thank you. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
One way, albeit longish ..
Try this slight revision to my earlier suggestion in the other branch: =COUNTIF(C$91:C$103,"<")-SUMPRODUCT((ISNUMBER(SEARCH("Barbie Doll",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))+(ISNUMBER(SEARCH("Beanie Babies",C$91:C$103))*ISERROR(SEARCH("cancel",C$91: C$103)))+(ISNUMBER(SEARCH("Toy Cars",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103))))-COUNTIF(C$91:C$103,"*"&"cancel"&"*") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "mldancing" wrote: Example: Barbie Doll Beanie Babies Boats - Cancel Stuffed Toys - Cancel Teddy Bears So the answer should just be 1 (count for 1 occurance of Teddy Bear only). Since I don't want to count Barbie Doll, Beanie Babies, and anything that has the word "Cancel". |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
OK, now I see why you had this in your other formula:
*ISERROR(SEARCH("cancel",C$91:C$103)) Try this: A91:A94 = Barbie Doll, Beanie Babies, Toy Cars, cancel =SUMPRODUCT(--(C91:C103<""),--(ISERROR(SEARCH(A91:A93,C91:C103))))-COUNTIF(C91:C103,"*"&A94&"*") Biff "mldancing" wrote in message ... Example: Barbie Doll Beanie Babies Boats - Cancel Stuffed Toys - Cancel Teddy Bears So the answer should just be 1 (count for 1 occurance of Teddy Bear only). Since I don't want to count Barbie Doll, Beanie Babies, and anything that has the word "Cancel". "T. Valko" wrote: Let me see if I got this straight..... You want to count the number of cells in a range that *do not* contain any of the following: Barbie Doll Beanie Babies Toy Cars cancel Make a list of those strings in a range of cells: A91 = Barbie Doll A92 = Beanie Babies A93 = Toy Cars A94 = cancel Then: =SUMPRODUCT(--(ISERROR(SEARCH(A91:A94,C91:C103)))) Biff I'm assuming that since you're using SEARCH in your formula these are substings. "mldancing" wrote in message ... There shouldn't be any. But if there is something that says "cancel", then the formula shouldn't count it. Thank you. "T. Valko" wrote: Are there any empty/blank cells in the range? Biff "mldancing" wrote in message ... I have this formula that count the number of times "Barbie Doll" occurs: =SUMPRODUCT((ISNUMBER(SEARCH("Barbie Doll",C$91:C$103))*ISERROR(SEARCH("cancel",C$91:C$ 103)))) How can I change the formula to count everything else OTHER THAN these items: Barbie Doll, Beanie Babies, Toy Cars? Please help. Thank you. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
Biff,
Assuming sample data within C91:C103 is: Barbie Doll - cancel Beanie Babies - cancel Boats - cancel Stuffed Toys - cancel Teddy Bears Toy Cars Lego <rest of range blank your formula returns: 1, while mine returns: 2 From my understanding of the OP's specs, the count based on the sample data above should be 2, viz.: Teddy Bears & Lego -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "T. Valko" wrote: OK, now I see why you had this in your other formula: *ISERROR(SEARCH("cancel",C$91:C$103)) Try this: A91:A94 = Barbie Doll, Beanie Babies, Toy Cars, cancel =SUMPRODUCT(--(C91:C103<""),--(ISERROR(SEARCH(A91:A93,C91:C103))))-COUNTIF(C91:C103,"*"&A94&"*") Biff |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
Yeah, you're right.
Make that: =SUMPRODUCT(--(C91:C103<""),--(ISERROR(SEARCH(A91:A93,C91:C103))),--(ISNUMBER(SEARCH(A94,C91:C103)))) Biff "Max" wrote in message ... Biff, Assuming sample data within C91:C103 is: Barbie Doll - cancel Beanie Babies - cancel Boats - cancel Stuffed Toys - cancel Teddy Bears Toy Cars Lego <rest of range blank your formula returns: 1, while mine returns: 2 From my understanding of the OP's specs, the count based on the sample data above should be 2, viz.: Teddy Bears & Lego -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "T. Valko" wrote: OK, now I see why you had this in your other formula: *ISERROR(SEARCH("cancel",C$91:C$103)) Try this: A91:A94 = Barbie Doll, Beanie Babies, Toy Cars, cancel =SUMPRODUCT(--(C91:C103<""),--(ISERROR(SEARCH(A91:A93,C91:C103))))-COUNTIF(C91:C103,"*"&A94&"*") Biff |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
Well dang!
Disregard that last reply! Biff "T. Valko" wrote in message ... Yeah, you're right. Make that: =SUMPRODUCT(--(C91:C103<""),--(ISERROR(SEARCH(A91:A93,C91:C103))),--(ISNUMBER(SEARCH(A94,C91:C103)))) Biff "Max" wrote in message ... Biff, Assuming sample data within C91:C103 is: Barbie Doll - cancel Beanie Babies - cancel Boats - cancel Stuffed Toys - cancel Teddy Bears Toy Cars Lego <rest of range blank your formula returns: 1, while mine returns: 2 From my understanding of the OP's specs, the count based on the sample data above should be 2, viz.: Teddy Bears & Lego -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "T. Valko" wrote: OK, now I see why you had this in your other formula: *ISERROR(SEARCH("cancel",C$91:C$103)) Try this: A91:A94 = Barbie Doll, Beanie Babies, Toy Cars, cancel =SUMPRODUCT(--(C91:C103<""),--(ISERROR(SEARCH(A91:A93,C91:C103))))-COUNTIF(C91:C103,"*"&A94&"*") Biff |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
Testing with a slightly revised sample data within C91:C103 of:
Barbie Doll - cancel Beanie Babies - cancel Boats - cancel Stuffed Toys Teddy Bears Toy Cars Lego <rest of range blank reveals your amended formula returns: 1, while mine returns: 3 The count based on the sample data above should be 3, viz.: Stuffed Toys, Teddy Bears & Lego -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "T. Valko" wrote: Yeah, you're right. Make that: =SUMPRODUCT(--(C91:C103<""),--(ISERROR(SEARCH(A91:A93,C91:C103))),--(ISNUMBER(SEARCH(A94,C91:C103)))) Biff |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
Ok, after a good nights sleep........
This is my final answer <g A91:A94 = Barbie Doll, Beanie Babie, Toy Cars, cancel =SUMPRODUCT(--(C91:C103<""),--(ISERROR(SEARCH(A94,C91:C103))))-SUMPRODUCT(COUNTIF(C91:C103,A91:A93)) Biff "Max" wrote in message ... Testing with a slightly revised sample data within C91:C103 of: Barbie Doll - cancel Beanie Babies - cancel Boats - cancel Stuffed Toys Teddy Bears Toy Cars Lego <rest of range blank reveals your amended formula returns: 1, while mine returns: 3 The count based on the sample data above should be 3, viz.: Stuffed Toys, Teddy Bears & Lego -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "T. Valko" wrote: Yeah, you're right. Make that: =SUMPRODUCT(--(C91:C103<""),--(ISERROR(SEARCH(A91:A93,C91:C103))),--(ISNUMBER(SEARCH(A94,C91:C103)))) Biff |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
Think it's time for the OP to return to this thread and close-off
discussions. Will leave it to OP to check based on his actual data & follow through here with you <g. IMO, it works fine. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "T. Valko" wrote: Ok, after a good nights sleep........ This is my final answer <g A91:A94 = Barbie Doll, Beanie Babie, Toy Cars, cancel =SUMPRODUCT(--(C91:C103<""),--(ISERROR(SEARCH(A94,C91:C103))))-SUMPRODUCT(COUNTIF(C91:C103,A91:A93)) Biff |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SumProduct Question
Actually, I think the OP should use 2 columns:
Column 1 = items Column 2 = status (cancel or whatever else) Biff "Max" wrote in message ... Think it's time for the OP to return to this thread and close-off discussions. Will leave it to OP to check based on his actual data & follow through here with you <g. IMO, it works fine. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "T. Valko" wrote: Ok, after a good nights sleep........ This is my final answer <g A91:A94 = Barbie Doll, Beanie Babie, Toy Cars, cancel =SUMPRODUCT(--(C91:C103<""),--(ISERROR(SEARCH(A94,C91:C103))))-SUMPRODUCT(COUNTIF(C91:C103,A91:A93)) Biff |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct Question | Excel Discussion (Misc queries) | |||
| Sumproduct question | Excel Worksheet Functions | |||
| Sumproduct Question | Excel Worksheet Functions | |||
| Sumproduct question | Excel Worksheet Functions | |||
| Question about sumproduct | Excel Discussion (Misc queries) | |||
Linear Mode
