SumProduct Question

M

mldancing

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

mldancing

There shouldn't be any. But if there is something that says "cancel", then
the formula shouldn't count it.

Thank you.
 
T

T. Valko

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

Max

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))))
 
M

Max

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))))
 
M

mldancing

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".
 
M

Max

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"&"*")
 
T

T. Valko

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
 
M

Max

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
 
T

T. Valko

Yeah, you're right.

Make that:

=SUMPRODUCT(--(C91:C103<>""),--(ISERROR(SEARCH(A91:A93,C91:C103))),--(ISNUMBER(SEARCH(A94,C91:C103))))

Biff
 
T

T. Valko

Well dang!

Disregard that last reply!

Biff

T. Valko said:
Yeah, you're right.

Make that:

=SUMPRODUCT(--(C91:C103<>""),--(ISERROR(SEARCH(A91:A93,C91:C103))),--(ISNUMBER(SEARCH(A94,C91:C103))))

Biff
Click to expand...
 
M

Max

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
 
T

T. Valko

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
 
M

Max

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

T. Valko

Actually, I think the OP should use 2 columns:

Column 1 = items
Column 2 = status (cancel or whatever else)

Biff
 
Top