If this or that is not something and this is something, say this

  • Thread starter Thread starter mjones
  • Start date Start date
M

mjones

Hi,

I hope someone can help with this formula.

If AB7:AB195<>"Me" or
AC7:AC195<>"Me" or
AD7:AD195<>"Me" or
AE7:AE195<>"ME" or
AF7:AF195<>"ME"

and J7:J195=0

="Warning"

Thanks,

Michele
 
I hope someone can help with this formula.
If AB7:AB195<>"Me" or
AC7:AC195<>"Me" or
AD7:AD195<>"Me" or
AE7:AE195<>"ME" or
AF7:AF195<>"ME"

and J7:J195=0
="Warning"
Click to expand...

What does it mean for a __range__ to be not equal to something?

Two common interpretations: (a) if all in the range do not equal it;
or (b) if any one in the range does not equal it.

And what do you want if the condition for "Warning" is __not__ met?

One common choice: return a null string so that the cell appears
blank.

Finally, I notice that the 5 ranges are contiguous. If we can take
advantage of that, then:

=IF(AND(COUNTIF(AB7:AF195,"<>me")=0,COUNTIF(J7:J195,"<>0")),"Warning","")

returns "Warning" if any cell in AB7:AF195 is not "me" and if any cell
in J7:J195 is nonzero.

Note that comparison to "me" is caseless. Are you trying to make an
distinction between "Me" and "ME"?

Note: If you have Excel 2007, there might be a simpler(?) solution
using COUNTIFS. I don't have Excel 2007, so I cannot try it.

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.
 
What does it mean for a __range__ to be not equal to something?

Two common interpretations:  (a) if all in the range do not equal it;
or (b) if any one in the range does not equal it.

And what do you want if the condition for "Warning" is __not__ met?

One common choice:  return a null string so that the cell appears
blank.

Finally, I notice that the 5 ranges are contiguous.  If we can take
advantage of that, then:

=IF(AND(COUNTIF(AB7:AF195,"<>me")=0,COUNTIF(J7:J195,"<>0")),"Warning","")

returns "Warning" if any cell in AB7:AF195 is not "me" and if any cell
in J7:J195 is nonzero.

Note that comparison to "me" is caseless.  Are you trying to make an
distinction between "Me" and "ME"?

Note:  If you have Excel 2007, there might be a simpler(?) solution
using COUNTIFS.  I don't have Excel 2007, so I cannot try it.

PS:  For broader participation, you might want to post future
inquiries using the MS Answers Forums athttp://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum.  It's just that MS has ceased to
support the Usenet newsgroups.  Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.
Click to expand...

Wow; I didn't explain very well. Thank you for your detailed answer.

Every row between row 7 and 195 (more will be added as they happen),
must have the word me (a short form for a 25 character phrase) in any
one of columns AB to AF if column J is 0. If column J is 0 and me
doesn't exist in one of columns AB to AF, return the word warning in
the cell with this formula. I hope that makes more sense.

Oh, and bad me - I'm using Excel 2003. Sorry again.

I tried the formula you guessed and, with my poor description, I'm not
surprised that it did not work.

This is the formula that works, but it only checks for 'me' in column
AB. I don't know how to find 'me' in one of the 5 columns AB to AF.

=IF(SUMPRODUCT(--(TRANSACTIONS!AB7:TRANSACTIONS!AB195<>"me"),--
(TRANSACTIONS!J7:TRANSACTIONS!J195<>0)),"Warning","")

Thanks again for your help,

Michele
 
 If column J is 0 and me
doesn't exist in one of columns AB to AF, return the word warning in
the cell with this formula.  I hope that makes more sense.
Click to expand...

Yes. But....

This is the formula that works, but it only checks for 'me' in column
AB.  I don't know how to find 'me' in one of the 5 columns AB to AF.
=IF(SUMPRODUCT(--(TRANSACTIONS!AB7:TRANSACTIONS!AB195<>"me"),
--(TRANSACTIONS!J7:TRANSACTIONS!J195<>0)),"Warning","")
Click to expand...

I do not believe that formula does what your English description
says. And I believe your English description is correct. (Well,
almost. We'll get to that later.)

This is when it is best to start with a concrete example of data,
specifying the desired result.

Consider the following in A18:A33 and B18:B33, where "...." separates
columns and "--" indicates a blank cell. Note that I choose the
number of each case (in parentheses) so that we can tell which cases
SUMPRODUCT counts.

-----.....-- (1)
-----.... 0 (2)
-----.... 0
me.....-- (4)
me.....--
me.....--
me.....--
me.... 0 (8)
me.... 0
me.... 0
me.... 0
me.... 0
me.... 0

The following formula is equivalent to yours:

=SUMPRODUCT((A18:A33<>"me")*(B18:B33<>0))

Note that it returns 1, counting only the case of "----.....--" when
there is "me" and there is no 0.

I suspect you want to count at least 2 for the cases of "----.....0",
when there is 0 but no "me". That formula would be:

=SUMPRODUCT((A18:A33<>"me")*(B18:B33=0))

Alternatively, do you want to count 6 for the cases of "me....--" as
well as "----....0"; that is, when there is "me" but no 0 as well as
when there is 0 but no "me"?

That formula would be:

=SUMPRODUCT((A18:A33<>"me")*(B18:B33=0)
+(A18:A33="me")*(B18:B33<>0))

Now consider the case where we have 3 columns where we might find
"me" (B1:D16) along with 1 column where we might find 0 (E1:E16. The
left-most column (A1:A16) indicates the row number in order to
facility discussion here.

01....-----....-----....-----....--
02....-----....-----....-----....0
03....-----....-----....me....--
04....-----....-----....me....0
05....-----....me....-----....--
06....-----....me....-----....0
07....-----....me....me....--
08....-----....me....me....0
09....me....-----....-----....--
10....me....-----....-----....0
11....me....-----....me....--
12....me....-----....me....0
13....me....me....-----....--
14....me....me....-----....0
15....me....me....me....--
16....me....me....me....0

Question.... You wrote: " If column J is 0 and me doesn't exist in
one of columns AB to AF, return the word warning". Do you mean: if J
is 0: (a) there must be exactly one "me" in the corresponding row of
AB:AF; or (b) there must be at least one "me" (i.e. one or more "me")
in the corresponding row of AB:AF?

Condition #b is easier (for me) to write than #a. But the best I can
offer is (omitting the worksheet name for brevity; you can add it in):

=SUMPRODUCT((AB7:AB195<>"me")*(AC7:AC195<>"me")
*(AD7:AD195<>"me")*(AE7:AE195<>"me")*(AF7:AF195<>"me")*(J7:J195=0))

Read: count the number of rows when there is no "me" AB __and__ there
is no "me" in AC etc __and__ there is 0 in column J.

Substituting B1:B16<>"me", C1:C16<>"me", D1:D16<>"me" and E1:E16=0,
that returns 1 for my example data above. And we can determine that
it is counting only row 2 by temporarily entering "me" in any of the
columns therein.

This is when it would behoove you to heed my previous suggestion, to
wit....

For broader participation, you might want to post future inquiries
using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

Clever contributors there might have a much easier formula -- at least
easier to maintain as your needs change over time, even it might be
more difficult to enter and comprehend initially.

If/when you do post to the Answers Forum group, I suggest that you
include the example data that I provided, together with an answer to
my question regarding #a or #b above.
 
Yes.  But....


I do not believe that formula does what your English description
says.  And I believe your English description is correct.  (Well,
almost.  We'll get to that later.)

This is when it is best to start with a concrete example of data,
specifying the desired result.

Consider the following in A18:A33 and B18:B33, where "...." separates
columns and "--" indicates a blank cell.  Note that I choose the
number of each case (in parentheses) so that we can tell which cases
SUMPRODUCT counts.

-----.....--    (1)
-----.... 0    (2)
-----.... 0
me.....--    (4)
me.....--
me.....--
me.....--
me.... 0     (8)
me.... 0
me.... 0
me.... 0
me.... 0
me.... 0

The following formula is equivalent to yours:

=SUMPRODUCT((A18:A33<>"me")*(B18:B33<>0))

Note that it returns 1, counting only the case of "----.....--" when
there is "me" and there is no 0.

I suspect you want to count at least 2 for the cases of "----.....0",
when there is 0 but no "me".  That formula would be:

=SUMPRODUCT((A18:A33<>"me")*(B18:B33=0))

Alternatively, do you want to count 6 for the cases of "me....--" as
well as "----....0"; that is, when there is "me" but no 0 as well as
when there is 0 but no "me"?

That formula would be:

=SUMPRODUCT((A18:A33<>"me")*(B18:B33=0)
+(A18:A33="me")*(B18:B33<>0))

Now consider the case where we have 3 columns where we might find
"me" (B1:D16) along with 1 column where we might find 0 (E1:E16.  The
left-most column (A1:A16) indicates the row number in order to
facility discussion here.

01....-----....-----....-----....--
02....-----....-----....-----....0
03....-----....-----....me....--
04....-----....-----....me....0
05....-----....me....-----....--
06....-----....me....-----....0
07....-----....me....me....--
08....-----....me....me....0
09....me....-----....-----....--
10....me....-----....-----....0
11....me....-----....me....--
12....me....-----....me....0
13....me....me....-----....--
14....me....me....-----....0
15....me....me....me....--
16....me....me....me....0

Question....  You wrote:  " If column J is 0 and me doesn't exist in

one of columns AB to AF, return the word warning".  Do you mean:  if J
is 0:  (a) there must be exactly one "me" in the corresponding row of
AB:AF; or (b) there must be at least one "me" (i.e. one or more "me")
in the corresponding row of AB:AF?

Condition #b is easier (for me) to write than #a.  But the best I can
offer is (omitting the worksheet name for brevity; you can add it in):

=SUMPRODUCT((AB7:AB195<>"me")*(AC7:AC195<>"me")
*(AD7:AD195<>"me")*(AE7:AE195<>"me")*(AF7:AF195<>"me")*(J7:J195=0))

Read:  count the number of rows when there is no "me" AB __and__ there
is no "me" in AC etc __and__ there is 0 in column J.

Substituting B1:B16<>"me", C1:C16<>"me", D1:D16<>"me" and E1:E16=0,
that returns 1 for my example data above.  And we can determine that
it is counting only row 2 by temporarily entering "me" in any of the
columns therein.

This is when it would behoove you to heed my previous suggestion, to
wit....

For broader participation, you might want to post future inquiries
using the MS Answers Forums athttp://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum.  It's just that MS has ceased to
support the Usenet newsgroups.  Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

Clever contributors there might have a much easier formula -- at least
easier to maintain as your needs change over time, even it might be
more difficult to enter and comprehend initially.

If/when you do post to the Answers Forum group, I suggest that you
include the example data that I provided, together with an answer to
my question regarding #a or #b above.
Click to expand...

I'm sorry for the delay. Pressing matters kept me away.

You have listed so many options and it is best that my intention is
clear because I'm getting mixed up. Now I'm trying to remember what
my intention was (other than checking when people enter data that
doesn't make sense).

I have tried a few things you mentioned above, and then realized that
I got something backwards. I'm trying to say that if column J in NOT
0, 'me' must exist in at least one of column AB to AF. 'me' can be in
any one of those 5 columns. If it exists in more than one column, the
data will be stupid, but not a biggie so we don't need to check for
it. 'me' must be in at least one of the AB to AF columns if J is NOT
0.

In people speak, if J is not zero, the transaction (row) is an invoice
and someone owes us money. If someone owes us money, we need a note
to collect it (me for short, but it's really 'Collect payment/update
payment method') in one of our five note columns (AB to AF). Each
transaction can have up to five notes. If we're reminded to collect
payment more than once, that's not a biggie, but we must be reminded
at least once.

I hope you can help with this formula.

The closest formula you supplied was (you'll see I changed J to not
equal):
=SUMPRODUCT((AB7:AB195<>"me")*(AC7:AC195<>"me")*(AD7:AD195<>"me")*(AE7:AE195<>"me")*(AF7:AF195<>"me")*(J7:J195<>0))

The problem with this one is that it returns a number and I need a
text warning like 'Invoice due needs collect payment to finalize'.
Since the warning is in a text red cell, it will pop up when the
condition exists to notify the user.

Best regards and thanks for all your help, including the MS Answers
Forum which I have bookmarked for future use.

Michele
 
The problem with this one is that it returns a number and I need a
text warning like 'Invoice due needs collect payment to finalize'.
Since the warning is in a text red cell, it will pop up when the
condition exists to notify the user.
Click to expand...

Sorry. Since one of your previous postings demonstrated that you knew
how to put the SUMPRODUCT expression into the required IF construct, I
ass-u-me-d you would know how to apply the formula above. To wit:

=IF(SUMPRODUCT((AB7:AB195<>"me")*(AC7:AC195<>"me")
*(AD7:AD195<>"me")*(AE7:AE195<>"me")*(AF7:AF195<>"me")*(J7:J195<>0))>0,
"Warning","")

And of course, each range should be written in the form Transactions!
AB7:AB195, as per one of your earlier posting. I omitted the
'Transactions!' qualifier for brevity.

However....
I'm getting mixed up. [....]
In people speak, if J is not zero, the transaction (row) is an invoice
and someone owes us money. If someone owes us money, we need a
note to collect it (me for short, but it's really 'Collect payment/update
payment method') in one of our five note columns (AB to AF). Each
transaction can have up to five notes. If we're reminded to collect
payment more than once, that's not a biggie, but we must be reminded
at least once.
Click to expand...

Sorry to keep throwing out questions that add to your confusion,
but....

The formula above results in __one__ warning if there is __any__ row
(one or more) where column J is non-zero and "me" is not present in at
least one of the 5 columns AB:AF.

But given your description "in people speak", I wonder if you want a
warning __for_each__ row where column J is non-zero and "me" is not
present in at least one of the 5 columns AB:AF, resulting in as many
separate warnings as there are rows that meet these conditions.

If the latter is the case, the solution might be more straight-
forward.

Put the following formula into one cell and copy it down the column
for each Transactions row:

=IF(AND(Transactions!J7<>0,COUNTIF(Transactions!AB7:AF7,"me")=0),
"Warning","")

As you copy the formula down the column, J7 and AB7:AF7 change to J8
and AB8:AF8, etc.

You can put the formulas into any column, e.g. A1:A189 for rows 7:195.
 
The problem with this one is that it returns a number and I need a
text warning like 'Invoice due needs collect payment to finalize'.
Since the warning is in a text red cell, it will pop up when the
condition exists to notify the user.
Click to expand...

Sorry.  Since one of your previous postings demonstrated that you knew
how to put the SUMPRODUCT expression into the required IF construct, I
ass-u-me-d you would know how to apply the formula above.  To wit:

=IF(SUMPRODUCT((AB7:AB195<>"me")*(AC7:AC195<>"me")
*(AD7:AD195<>"me")*(AE7:AE195<>"me")*(AF7:AF195<>"me")*(J7:J195<>0))>0,
"Warning","")

And of course, each range should be written in the form Transactions!
AB7:AB195, as per one of your earlier posting.  I omitted the
'Transactions!' qualifier for brevity.

However....


I'm getting mixed up. [....]
In people speak, if J is not zero, the transaction (row) is an invoice
and someone owes us money.  If someone owes us money, we need a
note to collect it (me for short, but it's really 'Collect payment/update
payment method') in one of our five note columns (AB to AF).  Each
transaction can have up to five notes.  If we're reminded to collect
payment more than once, that's not a biggie, but we must be reminded
at least once.
Click to expand...

Sorry to keep throwing out questions that add to your confusion,
but....

The formula above results in __one__ warning if there is __any__ row
(one or more) where column J is non-zero and "me" is not present in at
least one of the 5 columns AB:AF.

But given your description "in people speak", I wonder if you want a
warning __for_each__ row where column J is non-zero and "me" is not
present in at least one of the 5 columns AB:AF, resulting in as many
separate warnings as there are rows that meet these conditions.

If the latter is the case, the solution might be more straight-
forward.

Put the following formula into one cell and copy it down the column
for each Transactions row:

=IF(AND(Transactions!J7<>0,COUNTIF(Transactions!AB7:AF7,"me")=0),
"Warning","")

As you copy the formula down the column, J7 and AB7:AF7 change to J8
and AB8:AF8, etc.

You can put the formulas into any column, e.g. A1:A189 for rows 7:195.
Click to expand...

Hi Again,

The formula must only be in one cell (one warning) to warn if any rows
(columns) have the condition. It's hard to explain why, but one
reason this works is that we check for errors after entering only a
few transactions so we'll know one of the new rows has a problem.
There will be several thousand rows (transactions). There are
currently about 50 checks in the spreadsheet in a separate checks and
balances tab.

Can you help with a formula like this?

Thanks again, very much!

Michele
 
The formula must only be in one cell (one warning) to warn if any rows
(columns) have the condition. [....]
Can you help with a formula like this?
Click to expand...

Thought I did already. What's wrong with:

=IF(SUMPRODUCT((AB7:AB195<>"me")*(AC7:AC195<>"me")
*(AD7:AD195<>"me")*(AE7:AE195<>"me")*(AF7:AF195<>"me")*(J7:J195<>0))>0,
"Warning","")

As noted previously change each range reference from the form
AB7:AB195 to Transactions!AB7:AB195 if the formula goes on a different
worksheet, as suggested by one of your previous postings.
 
The formula must only be in one cell (one warning) to warn if any rows
(columns) have the condition. [....]
Can you help with a formula like this?
Click to expand...

Thought I did already.  What's wrong with:

=IF(SUMPRODUCT((AB7:AB195<>"me")*(AC7:AC195<>"me")
*(AD7:AD195<>"me")*(AE7:AE195<>"me")*(AF7:AF195<>"me")*(J7:J195<>0))>0,
"Warning","")

As noted previously change each range reference from the form
AB7:AB195 to Transactions!AB7:AB195 if the formula goes on a different
worksheet, as suggested by one of your previous postings.
Click to expand...

That worked brilliantly. I'm sorry, I was confused. I'm sure to use
this formula in the future. Thank you for your patience and kindness.
 
Back
Top