Need help in formula

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hello, I was wondering if anyone has a solution for the
following formula. What I want to do is count any cell in
column M with the test string of "DROP" regardless if it's
in CAPS or lower caps. The formula I have is case
sensitive which I do not want. Any help would be awesome
thanks.

=SUMPRODUCT(ISNUMBER(FIND("DROP",'Shipped Data'!M2:M20000))
*('Shipped Data'!I2:I20000=
{"JXO135","CAK178","DRA022","MLS123","JMP158","BHH066","CXB
142","DDB184","LXR104","MJV960","BXB111","RXP153","HXR146",
"MTH129","DES084","JMB107","IHG044"}))
 
Hi
try replacing FIND with SEARCH:

=SUMPRODUCT(ISNUMBER(SEARCH("DROP",'Shipped Data'!M2:M20000))
*('Shipped
Data'!I2:I20000={"JXO135","CAK178","DRA022","MLS123","JMP158","BHH066",
"CXB
142","DDB184","LXR104","MJV960","BXB111","RXP153","HXR146",
"MTH129","DES084","JMB107","IHG044"}))
 
=SUMPRODUCT(--ISNUMBER(SEARCH("DROP",'Shipped Data'!M2:M20000)),
--ISNUMBER(MATCH('Shipped Data'!I2:I20000,
{"JXO135","CAK178","DRA022","MLS123","JMP158","BHH066","CXB
142","DDB184","LXR104","MJV960","BXB111","RXP153","HXR146",
"MTH129","DES084","JMB107","IHG044"},0)))

You can even substitute in the above formula a range for
{"JXO135","CAK178","DRA022","MLS123","JMP158","BHH066","CXB
142","DDB184","LXR104","MJV960","BXB111","RXP153","HXR146",
"MTH129","DES084","JMB107","IHG044"} which houses the members of this array.
 
works beautifully!! thanks, I have one more problem if you
do not mind me asking... now I'm trying to add up column Q
if the criteria is met. I have the following formula

=SUMPRODUCT(ISNUMBER(SEARCH("DROP",'Shipped Data'!
M2:M20000))*('Shipped Data'!I2:I20000=
{"JXO135","IHG044","CAK178","DRA022","MLS123","JMP158","BHH
066","CXB142","DDB184","LXR104","MJV960","BXB111","RXP153",
"HXR146","MTH129","DES084"}))*('Shipped Data'!Q2:Q20000)

the problem is that the total is 839,573.00 but only
10,725.00 is returning as the sum. Are my parenthesis
incorrect?
 
No need for ISNUMBER or FIND/SEARCH:

=SUMPRODUCT(('Shipped Data'!M2:M200="DROP")*('Shipped Data'!I2:I200=
{"JXO135","CAK178","DRA022","MLS123","JMP158","BHH066","CXB142","DDB184",
"LXR104","MJV960","BXB111","RXP153","HXR146","MTH129","DES084","JMB107","
IHG044"}))
 
Hi
no your parenthesis are not correct :-)
Try
=SUMPRODUCT(ISNUMBER(SEARCH("DROP",'Shipped Data'!
M2:M20000))*('Shipped Data'!I2:I20000=
{"JXO135","IHG044","CAK178","DRA022","MLS123","JMP158","BHH
066","CXB142","DDB184","LXR104","MJV960","BXB111","RXP153",
"HXR146","MTH129","DES084"}),'Shipped Data'!Q2:Q20000)
 
Because you're not using the same dimensions for each of your arrays
(the second is 20,000 by 16, while the first and third are 20,000 by 1),
you can't use SUMPRODUCT's array multiplication - the multiplication has
to happen first, so instead of

,'Shipped Data'!Q2:Q20000


use

*'Shipped Data'!Q2:Q20000
 
=SUMPRODUCT(--ISNUMBER(SEARCH("DROP",'Shipped Data'!M2:M20000)),
--ISNUMBER(MATCH('Shipped Data'!I2:I20000,
{"JXO135","CAK178","DRA022","MLS123","JMP158","BHH066","CXB
142","DDB184","LXR104","MJV960","BXB111","RXP153","HXR146",
"MTH129","DES084","JMB107","IHG044"},0)),'Shipped Data'!Q2:Q20000)
 
Ok well I must be dumb cause it's still not working lol,
the formula I have is

=SUMPRODUCT(ISNUMBER(SEARCH("DROP",'Shipped Data'!
M2:M20000))*('Shipped Data'!I2:I20000=
{"JXO135","IHG044","CAK178","DRA022","MLS123","JMP158","BHH
066","CXB142","DDB184","LXR104","MJV960","BXB111","RXP153",
"HXR146","MTH129","DES084"})*'Shipped Data'!Q2:Q20000)

Still gives me a value# error.
 
Back
Top