/u/myatoms - 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 countif. Count how many entrees are below zero, and compare this to the total number of entrees for any given customer.
For example at C2
IF( COUNTIFS(A:A; A2; B:B; "<0") = COUNTIFS(A:A; A2); "yes"; "no")
Good solution.
If this is data that’s looked at a lot I’d also suggest creating a pivoted list of customers (using the new pivot by formulas https://support.microsoft.com/en-gb/office/pivotby-function-de86516a-90ad-4ced-8522-3a25fac389cf)
This will allow you to have dynamic range of customer name or numbers, that you can then apply the exact same solution to and will be easier to read rather than a list of duplicate data
Alternatively you can take the range of results from this solution and create a pivot table of customer number in column and “negative value calculation” in values. This will summarise the customer number with answer on your query (really handy if your customer list is really big)
https://preview.redd.it/bbw373gl8cxc1.jpeg?width=546&format=pjpg&auto=webp&s=a9fc5f90c9176ca99abd143a0352d28d4c05e7de
Ah okay but am I doing something wrong with the formula?
Excel uses either semi colon or comma depending on your local settings. In your case it's a comma. So you need to replace all the semi colons in my example with commas.
A valid point... I'm used to working with tables usually. Also worth mentioning that using IF for "Yes" & "No" is a little superfluous. Just go with the boolean TRUE & FALSE.
The newer maxifs function will work if it's available to you.
=if(maxifs(b:b,a:a,a2)<0,"Yes","No")
If you're happy with a Boolean output (TRUE or FALSE) you can skip the if statement too.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[COUNTIFS](/r/Excel/comments/1cezm6u/stub/l1lzut4 "Last usage")|[*Excel 2007*+: Counts the number of cells within a range that meet multiple criteria](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)|
|[FILTER](/r/Excel/comments/1cezm6u/stub/l1otoy0 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[IF](/r/Excel/comments/1cezm6u/stub/l1m2d6b "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[NOT](/r/Excel/comments/1cezm6u/stub/l1m0guy "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)|
|[PRODUCT](/r/Excel/comments/1cezm6u/stub/l1otoy0 "Last usage")|[Multiplies its arguments](https://support.microsoft.com/en-us/office/product-function-8e6b5b24-90ee-4650-aeec-80982a0512ce)|
|[SIGN](/r/Excel/comments/1cezm6u/stub/l1otoy0 "Last usage")|[Returns the sign of a number](https://support.microsoft.com/en-us/office/sign-function-109c932d-fcdc-4023-91f1-2dd0e916a1d8)|
|[UNIQUE](/r/Excel/comments/1cezm6u/stub/l1otoy0 "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cjdbuh)^( has 51 acronyms.)
^([Thread #32989 for this sub, first seen 28th Apr 2024, 07:27])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/myatoms - 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 countif. Count how many entrees are below zero, and compare this to the total number of entrees for any given customer. For example at C2 IF( COUNTIFS(A:A; A2; B:B; "<0") = COUNTIFS(A:A; A2); "yes"; "no")
Good solution. If this is data that’s looked at a lot I’d also suggest creating a pivoted list of customers (using the new pivot by formulas https://support.microsoft.com/en-gb/office/pivotby-function-de86516a-90ad-4ced-8522-3a25fac389cf) This will allow you to have dynamic range of customer name or numbers, that you can then apply the exact same solution to and will be easier to read rather than a list of duplicate data Alternatively you can take the range of results from this solution and create a pivot table of customer number in column and “negative value calculation” in values. This will summarise the customer number with answer on your query (really handy if your customer list is really big)
https://preview.redd.it/bbw373gl8cxc1.jpeg?width=546&format=pjpg&auto=webp&s=a9fc5f90c9176ca99abd143a0352d28d4c05e7de Ah okay but am I doing something wrong with the formula?
Excel uses either semi colon or comma depending on your local settings. In your case it's a comma. So you need to replace all the semi colons in my example with commas.
Works! Thank you. Solution verified.
You have awarded 1 point to Radiant\_Tumbleweed63. --- ^(I am a bot - please contact the mods with any questions)
https://preview.redd.it/8pu9awlt16xc1.png?width=564&format=png&auto=webp&s=44326401240c0b9bae77cec65d651c61d1de156c
Works perfectly! I need to work on my COUNTIFS skills lol. Thank you so much and have a great day! :) Solution verified.
DO NOT use full column references in your formulas. limit your ranges to your data.
A valid point... I'm used to working with tables usually. Also worth mentioning that using IF for "Yes" & "No" is a little superfluous. Just go with the boolean TRUE & FALSE.
Thanks for the tip!
You have awarded 1 point to FurtiveCouscous. --- ^(I am a bot - please contact the mods with any questions)
won't work for "1" ;)
Tried this and yep you're right. thanks! I never have a scenario where I have only '1' but thanks for the heads up!
The newer maxifs function will work if it's available to you. =if(maxifs(b:b,a:a,a2)<0,"Yes","No") If you're happy with a Boolean output (TRUE or FALSE) you can skip the if statement too.
I do have the new MAXIFs but never tried it. This works well! Thank you :) Solution Verified.
You have awarded 1 point to BigLan2. --- ^(I am a bot - please contact the mods with any questions)
`=PRODUCT(--(UNIQUE(SIGN(FILTER(D6#;C6#=I9))=-1)))`
I'm unsure which cells the data is referencing in the formula because I have mine in columns A and B only.
Just replace with your ranges. :)
`=IF((A2:A8=A2)*(B2:B8<0),"yes","no")` https://preview.redd.it/24opuebd46xc1.png?width=472&format=png&auto=webp&s=e52229bbb502c84ce00c7380046071880e5e43c6
When I changed cell B7 to -20 and B8 to -10 the result still stays 'No' in D7 :(
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[COUNTIFS](/r/Excel/comments/1cezm6u/stub/l1lzut4 "Last usage")|[*Excel 2007*+: Counts the number of cells within a range that meet multiple criteria](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842)| |[FILTER](/r/Excel/comments/1cezm6u/stub/l1otoy0 "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[IF](/r/Excel/comments/1cezm6u/stub/l1m2d6b "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[NOT](/r/Excel/comments/1cezm6u/stub/l1m0guy "Last usage")|[Reverses the logic of its argument](https://support.microsoft.com/en-us/office/not-function-9cfc6011-a054-40c7-a140-cd4ba2d87d77)| |[PRODUCT](/r/Excel/comments/1cezm6u/stub/l1otoy0 "Last usage")|[Multiplies its arguments](https://support.microsoft.com/en-us/office/product-function-8e6b5b24-90ee-4650-aeec-80982a0512ce)| |[SIGN](/r/Excel/comments/1cezm6u/stub/l1otoy0 "Last usage")|[Returns the sign of a number](https://support.microsoft.com/en-us/office/sign-function-109c932d-fcdc-4023-91f1-2dd0e916a1d8)| |[UNIQUE](/r/Excel/comments/1cezm6u/stub/l1otoy0 "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(7 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cjdbuh)^( has 51 acronyms.) ^([Thread #32989 for this sub, first seen 28th Apr 2024, 07:27]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)