Hello,
How to search multiple text strings in array. This one works:
=SUMPRODUCT(ISERR(SEARCH("Kim Jong-un",$D$8:$D$1440))*($E$8:$L$1440)*ISERR(SEARCH("Asteroid2012TC4",$D$8:$D$1440)))
This one sum all except Kim's and October12 asteroid.
But how come this one doesn't work:
=SUMPRODUCT(ISERR(SEARCH({"Kim Jong-un","Asteroid2012TC4"},$D$8:$D$1440))*($E$8:$L$1440))
This gives wrong result.
Thanks!
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
This could work, but problem is I want anything with asteroid and kim including.
{=SUM(IF($D$8:$D$1440="Kim Jong-Un",$E$8:$L$1440),IF($D$8:$D$1440="Asteroid2012TC4",$E$8:$L$1440))}ctrl-sh-Ent
This formula doesn't alow wildcards *?
May be try
Formula:
Please Login or Register to view this content.
confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Or try
...Formula:Please Login or Register to view this content.
I didn't tested your formulae, but.....in E8:L1440 will maybe also be text, so, SUMP...will spit error, and, I want search anything in D containing kim or asteroid.
Attach you sample workbook with expected result. We are not here to give you solution on guessing as you are not paying to us.
Sorry........
TryYour formulas are attempting to do math operations on text. That returns errors. The N function in this formula returns text as 0s. Since the argument B1:D5 is a range it needs to be coerced into an array. The "+" is a way to do this.Formula:Please Login or Register to view this content.
Dave
Thank you VERY much, N is awesome, but SUMPRODUCT doesn't alow wildcards, i can0t use A8&A9 as reference, i need "*Kim*" and "*asteroid*"
Thank you all VERY much, Excel is awesome!
=SUMPRODUCT(ISERR(SEARCH({"*kim*","*asteroid*"},$D$8:$D$1440))*N(+$E$8:$L$1440))
This sum number values, N handles text cell values, Search is looking rows not containing kim and asteroid. The other way around is using NOT(ISERR(SEARCH.... when we want sum values for kim and asteroid.
THANKS!
Try this. SUMIF wll take wildcards.Formula:Please Login or Register to view this content.
Thanks! Not working, it's only picking up first B column not all 3 columns(B1:D5) Cheers!
Yup. My bad. Thisor array enter this If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Nope. First formula is too long, i have 8 columns, second one not working, it gives 0 Cheers!
Please show us the data.
If you are not familiar with MMULT it requires the same number of columns in the range of the first argument as the number of rows in the second argument. I find it easy to make mistakes. Additionally the TRUEs/FALSEs have to be coerced into their underlying numeric values 1/0. The "--" is one way to do that.
Last edited by FlameRetired; 09-23-2017 at 04:28 PM. Reason: spelling
Acctually I was wrong...
Post#10 formula =SUMPRODUCT(ISERR(SEARCH({"*kim*","*asteroid*"},$D$8:$D$1440))*N(+$E$8:$L$1440))
is not working, it's only picking up first couple of rows which are above kim or asteroid.
So, the only working solution is: =SUMPRODUCT(ISERR(SEARCH("kim",$D$8:$D$1440))*N(+$E$8:$L$1440))*ISERR(SEARCH("asteroid",$D$8:$D$1440)))
I lefted out * because Search finds anything with kim asteroid.
Last edited by B.W.B.; 09-26-2017 at 05:56 PM.
B.W.B.
Thank you for the feedback. Glad to hear you found solution.
Yeah, but, i don0t understand why the hell Search isn't working, why it can't search two criteria? {"kim","asteroid"}
It does search two criteria.
In the original upload enter just this part
=SEARCH({"kim","asteroid"},A1:A5)
Click in the formula bar and hit the F9 function key.
You will see this array:
{#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,1}
There are two 1s in that array. Look carefully. If you are not aware of it commas are array column separators and semi-colons are array row separators. This is also true of array constants like {"kim","asteroid"}.
By introducing that column separated constant (in row orientation) to the single column of rows in A1:A5 "kim" is first located at row 2 and column 1. This is so because SEARCH returns the first instance of a match. Since "kim" is in the first "column" of the constant and is in the second row of the range A1:A5 that is what it returns. Follow the same logic for "asteroid". It's in the second "column" of the constant. Where is it in the array?
The net effect of your formula is that you are including more than one 2-dimensional of unequal dimensions into the formula ... a two dimensional array (5x2) and a two dimensional range B1:D5 (5x3). SUMPRODUCT does not like that. It can handle one multi-dimensional array size at a time. After that you need MMULT. Even then there are rules. I was mystified at the report of MMULT not working with larger data and requested additional information.
Did this help?
Last edited by FlameRetired; 09-26-2017 at 08:42 PM.
Yes, thanks!
Correction!
I acctually get different result with Search: {#VALUE!,#N/A;1,#N/A;#N/A,#N/A;#N/A,#N/A;#N/A,#N/A}
That's odd. Are you certain of the spellings?
I was working off of this from the upload post #7.
Not working, Search not functioning as it should, or I'm wrong?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks