Formula help needed

[Solved/Closed]
Report
Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013
-
Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013
-
Hello,

I am in need of a formula.

I own a business in which I receive payments from my clients in various ways. I also have multiple locations.

I was lucky enough to find this formula: =SUM(IF(F2:F50="LOCATION",B2:B50))
which allows me to total payments by location (not sure if it is the best formula, since it's an array).

Now, I am looking to do the same thing, but, I want to deduct the amount in column B IF the value in column E equals a certain text.


4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I am not sure why you would you need an array formula if all you want to add those rows where you have "LOCATION"
if that is the case then instead of
SUM(IF(F2:F50="LOCATION",B2:B50))

use
SUMIF(F2:F50,"LOCATION",B2:B50)

Now on to your question. I am not sure I got what you meant by "deduct the amount in column B".

if you are saying that you want to add B2:B50 if F2:F50 is location and not some text in E2:E50 then in that case you can use
=SUMPRODUCT(1 * (F2:F50="LOCATION") * (E2:E50<>"SOME OTHER TEXT") * (B2:B50))
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013

Yes, by deduct, I really meant exclude... Sorry..

And all of your formulas worked perfectly! Thank you very much for the quick reply!
Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013

Now, this whole spreadsheet has me thinking...

What if I would want the value for those fields that the formula is executed in to change font colors based on whether or not the value is a) lower than an expected amount, b) the same as the expected amount, or c) higher than the expected amount.

So lets say we have the formula above executed in L4 to provide a total for location ABC. In L3 is an expected total of $1,250,000.00 Let's say L3 is $1,000,000.00. So, since it is less than what was expected in L4, I want the font color to be red. If L4 is the same as L3, I want the font color to be green. If L4 is higher than L3, I want the font color to be blue.

Is this possible?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Yes it can be done. In excel it is called "Conditional Formatting"
Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013

I have found the Conditional Formatting option (Format, Conditional Formatting), but I can't seem to figure out the rule.

I clicked on + to add a rule. I then choose 3 Color Scale. From there I am not sure if I am supposed to choose number, lowest value, highest value, or what... I did numbers for all three, selected the colors for the font, but I can't seem to get it to work correctly...
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Based on what you said
lets say
L4 = sum as per formula
L3 = expected value

You would need three rules. When you go to conditional format, you need to use formula
for blue, you need to enter =L4>L3
for RED, you need to enter =L4<L3
for GREEN, you need to enter =L4=L3
Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013

I have entered three separate rules. Each one is set up as Classic, Use a formula to determine which cells to format. I have chosen the font color and fill color and have entered the formula in the appropriate cell; however, the font color in my sheet hasn't changed...

And why mark solved without allowing me, the OP, to mark it solved?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I marked it solved as your query was "Now, I am looking to do the same thing, but, I want to deduct the amount in column B IF the value in column E equals a certain text."

and you said "
And all of your formulas worked perfectly! Thank you very much for the quick reply
!"

Conditional formatting was add on which I believe should have been another question.

But any ways on your color not showing, did you select any color would be obvious question to ask. If you did, then do this. Enter your condtional formula on a sheet and see if you get "True" or "False" as per your expectation. If not, you would need to see why i guess
Posts
6
Registration date
Sunday March 17, 2013
Status
Member
Last seen
March 20, 2013

I deleted and reapplied the rules, and for some reason they started working. I didn't change anything, so I am not sure why it didn't work the first time...

Thank you for all of your help!