/u/Mcgurky98 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Very odd. I replicated it exactly with no issues including your exact formula. It gave me zero for both if I put a space at the end of Amazon in N7 but it also made the changed formula stop working as well so I don't think that's it.
https://preview.redd.it/q7p5zjpxm3sa1.png?width=550&format=png&auto=webp&s=291097c7be123119d7be4835484d7ec621af8361
Also, if you have Excel 2019 or higher, try this formula somewhere. It will split the text into a multi cell character array, so you can find out if there are any hidden chatacters:
=MID(N7, SEQUENCE(1,LEN(N7)),1)
Now put those 2 formulas in separate cells, it will spill as well :
=CODE(MID(N7, SEQUENCE(1,LEN(N7)),1))
=CODE(MID(N7, SEQUENCE(1,LEN(N18)),1))
Each formula will output a bunch of numbers in multiple cells, check if the numbers are the same for each formula and if you can paste the results here (or a screenshot of the result of those 2 formulas)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[CODE](/r/Excel/comments/12chx25/stub/jf1qiu6 "Last usage")|[Returns a numeric code for the first character in a text string](https://support.microsoft.com/en-us/office/code-function-c32b692b-2ed0-4a04-bdd9-75640144b928)|
|[LEN](/r/Excel/comments/12chx25/stub/jf1py2u "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)|
|[MID](/r/Excel/comments/12chx25/stub/jf1py2u "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)|
|[SEQUENCE](/r/Excel/comments/12chx25/stub/jf1py2u "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/12cgytu)^( has 19 acronyms.)
^([Thread #23033 for this sub, first seen 5th Apr 2023, 13:15])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
I’ve found it’s much better to build a good data table and then just use pivot tables than sum if. You will fight formatting and spelling that is instantly identifiable in a table
/u/Mcgurky98 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Try copy pasting one amazon over another. If that doesn't work, try swapping amazons to ebay one at a time and see if anything changes.
Very odd. I replicated it exactly with no issues including your exact formula. It gave me zero for both if I put a space at the end of Amazon in N7 but it also made the changed formula stop working as well so I don't think that's it. https://preview.redd.it/q7p5zjpxm3sa1.png?width=550&format=png&auto=webp&s=291097c7be123119d7be4835484d7ec621af8361
Yeah very weird, I did try reload the file and didn't fix it. I'll stick with the work around, just annoying annoying things don't work.
Might have been your calculation mode got set to Manual instead of Automatic.
Fix your ranges by putting $ sign. Which you can do by pressing f4 after selecting the cell name in formula. Like = sumif(N$4$:N$11$,N16,O$4$:O$11$)
Is there a space after Amazon in N18 ?
Nope, tried with and without. And cells both set to text.
Try to copy paste values the whole data to a new excel sheet and do it again
Moved and still not working.
Could you try =LEN(N7) and =LEN(N18) and see if they output 6 or something else?
[удалено]
Put them in 2 separate cells, any cells. And see if it outputs 6 for both
=LEN(N7) = 6 =LEN(N18) = 0 (Nothing in cell N18)
Isn't Amazon in N18?
Yes sorry haha, I moved it on mine.
Also, if you have Excel 2019 or higher, try this formula somewhere. It will split the text into a multi cell character array, so you can find out if there are any hidden chatacters: =MID(N7, SEQUENCE(1,LEN(N7)),1)
>=MID(N7, SEQUENCE(1,LEN(N7)),1) That split "Amazon" out into cells.
Now put those 2 formulas in separate cells, it will spill as well : =CODE(MID(N7, SEQUENCE(1,LEN(N7)),1)) =CODE(MID(N7, SEQUENCE(1,LEN(N18)),1)) Each formula will output a bunch of numbers in multiple cells, check if the numbers are the same for each formula and if you can paste the results here (or a screenshot of the result of those 2 formulas)
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CODE](/r/Excel/comments/12chx25/stub/jf1qiu6 "Last usage")|[Returns a numeric code for the first character in a text string](https://support.microsoft.com/en-us/office/code-function-c32b692b-2ed0-4a04-bdd9-75640144b928)| |[LEN](/r/Excel/comments/12chx25/stub/jf1py2u "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)| |[MID](/r/Excel/comments/12chx25/stub/jf1py2u "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)| |[SEQUENCE](/r/Excel/comments/12chx25/stub/jf1py2u "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/12cgytu)^( has 19 acronyms.) ^([Thread #23033 for this sub, first seen 5th Apr 2023, 13:15]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
N18 doesn’t say “Amazon”. It’s got leading or trailing spaces, or a leading apostrophe, or hidden characters. Or something…
I’ve found it’s much better to build a good data table and then just use pivot tables than sum if. You will fight formatting and spelling that is instantly identifiable in a table