Related:

- Formula help needed
- Spreadsheet formula - Articles
- Excel @ in formula ✓ - Forum - Excel
- 1st, 2nd, 3rd position formula in excel ✓ - Forum - Office Software
- How to copy data from one sheet to another in excel using formula - How-To - Excel
- Excel formula if cell contains text then return value in another cell ✓ - Forum - Excel

## 4 replies

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

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))

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))

cromwelld

- 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!

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

cromwelld

- 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?

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?

cromwelld

- 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...

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...

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

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

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

cromwelld

- 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?

And why mark solved without allowing me, the OP, to mark it solved?

rizvisa1

- Posts
- 4476
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- August 2, 2020

I marked it solved as your query was "

and you said "

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

*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