| 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
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. |
|
#6
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". |
|
#7
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. |
|
#8
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. |
|
#9
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. |
| 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) | |||
Hybrid Mode
