SUMIF

[Mel]

Using the SUMIF function, i would like answers returned that are <> to be in
red font, and when the answer is = (ie the correct answer), for it to be in
black font.

is there a way of conditional font formatting?

Thanks in advance.

Mel

 

[JLatham]

It kind of depends upon how your SUMIF() formula is set up. If it is set up
to get the criteria from a cell [ like =SUMIF(A2:A9,$D$2,B2:B9)] rather than
having the criteria coded into the formula [ like =SUMIF(A2:A9,"G",B2:B9)]
then it can be done.

Assume a table like I used in my example formulas: the IF range is in A2:A9
with the numbers to be SUMmed in B2:B9 and cell D2 is where we can type in
the 'criteria' value for the SUMIF() formula to use.

Then you can select the values in column B and use Conditional Format and
use the "Formual Is" setting and set up this formula:
=$A2<>$D$2 and set the font color to red in that situation. Notice that I
removed the $ that Excel probably put between the A and the 2 in the formula
(it tried to write it as =$A$2<>$D$2 initially) before committing the formula
with the [OK] button.

Hope this helps you with the problem.

 

[Mel]

Thanks so much for your speedy response! unfortunately i am blonde, and am
still in the initial stages of bumbling my way around Excel, so some of your
reply is over my head!
I am using a number of sums that are all formatted similarly to this:

=IF(NOT(B7<=0.0375), "Over Limit", "Within Limit")

Does this mean that I can't colour the words red, because it isn't a cell
reference sum like the one you use as an example?

Thanks again.
Mel

It kind of depends upon how your SUMIF() formula is set up. If it is set up
to get the criteria from a cell [ like =SUMIF(A2:A9,$D$2,B2:B9)] rather than
having the criteria coded into the formula [ like =SUMIF(A2:A9,"G",B2:B9)]
then it can be done.

Assume a table like I used in my example formulas: the IF range is in A2:A9
with the numbers to be SUMmed in B2:B9 and cell D2 is where we can type in
the 'criteria' value for the SUMIF() formula to use.

Then you can select the values in column B and use Conditional Format and
use the "Formual Is" setting and set up this formula:
=$A2<>$D$2 and set the font color to red in that situation. Notice that I
removed the $ that Excel probably put between the A and the 2 in the formula
(it tried to write it as =$A$2<>$D$2 initially) before committing the formula
with the [OK] button.

Hope this helps you with the problem.

Using the SUMIF function, i would like answers returned that are <> to be in
red font, and when the answer is = (ie the correct answer), for it to be in
black font.

is there a way of conditional font formatting?

Thanks in advance.

Mel

 

[Dave Peterson]

First, I think I'd change the if condition:

=IF(NOT(B7<=0.0375), "Over Limit", "Within Limit")
to
=IF(B7>0.0375,"Over Limit","Within Limit")

I think it makes it easier to read/understand.

Then you could use conditional formatting:
Cell Value is:
equal to:
Over limit

and give it a nice format.

Then click add and do the same with "within limit"

This will format the entire cell with the format you choose. (You can't change
just the format for the words Over or Within.)

Thanks so much for your speedy response! unfortunately i am blonde, and am
still in the initial stages of bumbling my way around Excel, so some of your
reply is over my head!
I am using a number of sums that are all formatted similarly to this:

=IF(NOT(B7<=0.0375), "Over Limit", "Within Limit")

Does this mean that I can't colour the words red, because it isn't a cell
reference sum like the one you use as an example?

Thanks again.
Mel

It kind of depends upon how your SUMIF() formula is set up. If it is set up
to get the criteria from a cell [ like =SUMIF(A2:A9,$D$2,B2:B9)] rather than
having the criteria coded into the formula [ like =SUMIF(A2:A9,"G",B2:B9)]
then it can be done.

Assume a table like I used in my example formulas: the IF range is in A2:A9
with the numbers to be SUMmed in B2:B9 and cell D2 is where we can type in
the 'criteria' value for the SUMIF() formula to use.

Then you can select the values in column B and use Conditional Format and
use the "Formual Is" setting and set up this formula:
=$A2<>$D$2 and set the font color to red in that situation. Notice that I
removed the $ that Excel probably put between the A and the 2 in the formula
(it tried to write it as =$A$2<>$D$2 initially) before committing the formula
with the [OK] button.

Hope this helps you with the problem.

Using the SUMIF function, i would like answers returned that are <> to be in
red font, and when the answer is = (ie the correct answer), for it to be in
black font.

is there a way of conditional font formatting?

Thanks in advance.

Mel

 

[Mel]

Perfect - thanks heaps to both of you!

First, I think I'd change the if condition:

=IF(NOT(B7<=0.0375), "Over Limit", "Within Limit")
to
=IF(B7>0.0375,"Over Limit","Within Limit")

I think it makes it easier to read/understand.

Then you could use conditional formatting:
Cell Value is:
equal to:
Over limit

and give it a nice format.

Then click add and do the same with "within limit"

This will format the entire cell with the format you choose. (You can't change
just the format for the words Over or Within.)

Thanks so much for your speedy response! unfortunately i am blonde, and am
still in the initial stages of bumbling my way around Excel, so some of your
reply is over my head!
I am using a number of sums that are all formatted similarly to this:

=IF(NOT(B7<=0.0375), "Over Limit", "Within Limit")

Does this mean that I can't colour the words red, because it isn't a cell
reference sum like the one you use as an example?

Thanks again.
Mel

It kind of depends upon how your SUMIF() formula is set up. If it is set up
to get the criteria from a cell [ like =SUMIF(A2:A9,$D$2,B2:B9)] rather than
having the criteria coded into the formula [ like =SUMIF(A2:A9,"G",B2:B9)]
then it can be done.

Assume a table like I used in my example formulas: the IF range is in A2:A9
with the numbers to be SUMmed in B2:B9 and cell D2 is where we can type in
the 'criteria' value for the SUMIF() formula to use.

Then you can select the values in column B and use Conditional Format and
use the "Formual Is" setting and set up this formula:
=$A2<>$D$2 and set the font color to red in that situation. Notice that I
removed the $ that Excel probably put between the A and the 2 in the formula
(it tried to write it as =$A$2<>$D$2 initially) before committing the formula
with the [OK] button.

Hope this helps you with the problem.

:

Using the SUMIF function, i would like answers returned that are <> to be in
red font, and when the answer is = (ie the correct answer), for it to be in
black font.

is there a way of conditional font formatting?

Thanks in advance.

Mel

 

[JLatham]

Helps when we see the formula's you're using, as you provided for Dave
Peterson to assist with - I was kind of stabbing in the dark as to what you
needed.

Thanks so much for your speedy response! unfortunately i am blonde, and am
still in the initial stages of bumbling my way around Excel, so some of your
reply is over my head!
I am using a number of sums that are all formatted similarly to this:

=IF(NOT(B7<=0.0375), "Over Limit", "Within Limit")

Does this mean that I can't colour the words red, because it isn't a cell
reference sum like the one you use as an example?

Thanks again.
Mel

It kind of depends upon how your SUMIF() formula is set up. If it is set up
to get the criteria from a cell [ like =SUMIF(A2:A9,$D$2,B2:B9)] rather than
having the criteria coded into the formula [ like =SUMIF(A2:A9,"G",B2:B9)]
then it can be done.

Assume a table like I used in my example formulas: the IF range is in A2:A9
with the numbers to be SUMmed in B2:B9 and cell D2 is where we can type in
the 'criteria' value for the SUMIF() formula to use.

Then you can select the values in column B and use Conditional Format and
use the "Formual Is" setting and set up this formula:
=$A2<>$D$2 and set the font color to red in that situation. Notice that I
removed the $ that Excel probably put between the A and the 2 in the formula
(it tried to write it as =$A$2<>$D$2 initially) before committing the formula
with the [OK] button.

Hope this helps you with the problem.

Using the SUMIF function, i would like answers returned that are <> to be in
red font, and when the answer is = (ie the correct answer), for it to be in
black font.

is there a way of conditional font formatting?

Thanks in advance.

Mel

 

[Dave Peterson]

Without your question to Mel and his/her response, I wouldn't have ventured a
guess.

Helps when we see the formula's you're using, as you provided for Dave
Peterson to assist with - I was kind of stabbing in the dark as to what you
needed.

Thanks so much for your speedy response! unfortunately i am blonde, and am
still in the initial stages of bumbling my way around Excel, so some of your
reply is over my head!
I am using a number of sums that are all formatted similarly to this:

=IF(NOT(B7<=0.0375), "Over Limit", "Within Limit")

Does this mean that I can't colour the words red, because it isn't a cell
reference sum like the one you use as an example?

Thanks again.
Mel

It kind of depends upon how your SUMIF() formula is set up. If it is set up
to get the criteria from a cell [ like =SUMIF(A2:A9,$D$2,B2:B9)] rather than
having the criteria coded into the formula [ like =SUMIF(A2:A9,"G",B2:B9)]
then it can be done.

Assume a table like I used in my example formulas: the IF range is in A2:A9
with the numbers to be SUMmed in B2:B9 and cell D2 is where we can type in
the 'criteria' value for the SUMIF() formula to use.

Then you can select the values in column B and use Conditional Format and
use the "Formual Is" setting and set up this formula:
=$A2<>$D$2 and set the font color to red in that situation. Notice that I
removed the $ that Excel probably put between the A and the 2 in the formula
(it tried to write it as =$A$2<>$D$2 initially) before committing the formula
with the [OK] button.

Hope this helps you with the problem.

:

Using the SUMIF function, i would like answers returned that are <> to be in
red font, and when the answer is = (ie the correct answer), for it to be in
black font.

is there a way of conditional font formatting?

Thanks in advance.

Mel