IF Statement based on Conditional format

[Guest]

Hi all,

I have a sheet showing employee activities. It has a conditional format
based on the activity (i.e. "break" = yellow).

Now, I have a total collum counting all relevant activities (which are
displayed in numerical format). Because I dont want to include all
activities, I have to do a sum and select all relevant cells per employee.

I was wondering if I can do some sort of IF statement. i.e.:

when row 34 has a cell with a yellow fill conditional format, then SUM B2 +
all cells with the yellow conditional format.

or

when row 36 has a cell with a purple fill conditional format, then don't do
anything (or return 0)

I doubt if this is possible, but any suggestion are greatly appriciated.

 

[Ron Rosenfeld]

Hi all,

I have a sheet showing employee activities. It has a conditional format
based on the activity (i.e. "break" = yellow).

Now, I have a total collum counting all relevant activities (which are
displayed in numerical format). Because I dont want to include all
activities, I have to do a sum and select all relevant cells per employee.

I was wondering if I can do some sort of IF statement. i.e.:

when row 34 has a cell with a yellow fill conditional format, then SUM B2 +
all cells with the yellow conditional format.

or

when row 36 has a cell with a purple fill conditional format, then don't do
anything (or return 0)

I doubt if this is possible, but any suggestion are greatly appriciated.

Instead of looking at the fill color, when the fill is determined by
Conditional Formatting, construct your IF statement to look at the same
condition that is being used in the Conditional Format formula.

e.g. =IF(Activity = "Break", ...)


Or, if you have your Time in one column, and your Activity Type in another,
then:

=SUMIF(Activity, "Break", Time)


--ron

 

[Guest]

I've created the following formula;

=SUM(E$37:T$37,AND(ISNUMBER(SEARCH("break",F$37)),F38=0.25),E38:T38)-1

So when excel sees there is a heading called "break" and there is a value of
"0.25" in that same collum, it will than count the whole row and give me a
total.

But there are some values that I don't want to be includes. i.e., a collum
with values with the header "EVT" should not be includes in this total.

There must be some way to use the above formula and make it longer to all
the values I don't want.

You can download a very small version of the file here:

http://www.box.net/shared/0jojm33ovq

The total (minus ABS + EVT) should be in collum A.

 

[Ron Rosenfeld]

Given your "small version" workbook, the following formula

=IF(SUMPRODUCT(ISNUMBER(SEARCH("break",$C$2:$R$2))*(C3:R3=0.25)),
SUMPRODUCT(ISERR(SEARCH("evt",$C$2:$R$2))*C3:R3),0)


should sum the rows where there is at least one heading of "break"=0.25 and EVT
does NOT appear at the heading.

If you also want to exclude ABS, then try:

=IF(SUMPRODUCT(ISNUMBER(SEARCH("break",$C$2:$R$2))*(C3:R3=0.25)),
SUMPRODUCT(ISERR(SEARCH("evt",$C$2:$R$2)*SEARCH("abs",$C$2:$R$2))*C3:R3),0)

But it seems to me that in column A you are excluding more than just EVT and
ABS as the values don't seem to add up.


--ron

 

[Guest]

Hi Ron,

Thanks for your help. I've worked it out now.

I'm using this formula:

=IF(SUMPRODUCT(ISNUMBER(SEARCH("break",$C$2:$R$2))*(C3:R3=0.25)),SUMPRODUCT(ISERR(SEARCH("evt",$C$2:$R$2))*(ISERR(SEARCH("ABS",$C$2:$R$2)))*C3:R3),0)

And can expand it perfectly with more arguments. Maybe you can help me one
step further. When the result of the above formule is lower than value 0.50,
I want it to display 0.00

Any suggestions on how to accomplish this?

 

[Ron Rosenfeld]

Hi Ron,

Thanks for your help. I've worked it out now.

I'm using this formula:

=IF(SUMPRODUCT(ISNUMBER(SEARCH("break",$C$2:$R$2))*(C3:R3=0.25)),SUMPRODUCT(ISERR(SEARCH("evt",$C$2:$R$2))*(ISERR(SEARCH("ABS",$C$2:$R$2)))*C3:R3),0)

And can expand it perfectly with more arguments. Maybe you can help me one
step further. When the result of the above formule is lower than value 0.50,
I want it to display 0.00

Any suggestions on how to accomplish this?

The solution that occurs to me now (after a fine meal and a significant amount
of adult beverages ) is:

=IF(formula<0.5,0,formula)

where "formula" is the above long formula.


--ron

 

[Guest]

Ron, it works.

Thanks for your help and thanks to those beverages