Function Help
 

I have a log that keeps track of rejects.
In one row the word "Reject" can appear in 3 columns
(Column J, M or P)of any combination.
Example: Row 5 can have "reject" in column P.
Row 6 can have "reject" in column J or M.
Row 7 can have "reject" in column J, M & P.
If "reject" appears in any one of these columns, I want
to return the value of 1. So, at the bottom of the
report, I want to count each time the word "reject"
appears in that row. Even though it appears once, twice
or three times, I want it counted once.

Any suggestions?
Thank you and Happy Holidays

Hi
one way:
=SUMPRODUCT(--((J1:J100="reject")+(M1:M100="reject")+(P1:P100="r eject)
0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Karen" schrieb im Newsbeitrag
...
I have a log that keeps track of rejects.
In one row the word "Reject" can appear in 3 columns
(Column J, M or P)of any combination.
Example: Row 5 can have "reject" in column P.
Row 6 can have "reject" in column J or M.
Row 7 can have "reject" in column J, M & P.
If "reject" appears in any one of these columns, I want
to return the value of 1. So, at the bottom of the
report, I want to count each time the word "reject"
appears in that row. Even though it appears once, twice
or three times, I want it counted once.

Any suggestions?
Thank you and Happy Holidays

One way:

Assume your data goes from row 2 to row 51 (i.e, 50 rows):

=50-SUMPRODUCT(--(J2:J51<"Reject"),--(M2:M51<"Reject"),
--(P2:P51<"Reject"))





In article ,
"Karen" wrote:

I have a log that keeps track of rejects.
In one row the word "Reject" can appear in 3 columns
(Column J, M or P)of any combination.
Example: Row 5 can have "reject" in column P.
Row 6 can have "reject" in column J or M.
Row 7 can have "reject" in column J, M & P.
If "reject" appears in any one of these columns, I want
to return the value of 1. So, at the bottom of the
report, I want to count each time the word "reject"
appears in that row. Even though it appears once, twice
or three times, I want it counted once.

Any suggestions?
Thank you and Happy Holidays

If there can be no reject in K, L,N, O you can use

=(COUNTIF(J7:P7,"reject")0)*1

if there can be reject in the other columns

=(SUMPRODUCT((MOD(COLUMN(J7:P7),3)=1)*(J7:P7="reje ct"))0)*1



Regards,

Peo Sjoblom

"Karen" wrote:

I have a log that keeps track of rejects.
In one row the word "Reject" can appear in 3 columns
(Column J, M or P)of any combination.
Example: Row 5 can have "reject" in column P.
Row 6 can have "reject" in column J or M.
Row 7 can have "reject" in column J, M & P.
If "reject" appears in any one of these columns, I want
to return the value of 1. So, at the bottom of the
report, I want to count each time the word "reject"
appears in that row. Even though it appears once, twice
or three times, I want it counted once.

Any suggestions?
Thank you and Happy Holidays

I used your formula and adjusted the it with my ranges
and I'm getting the error #VALUE! Any suggestions?
Would it be easier to say that the range is J3:P57
instead of using the individual columns?
-----Original Message-----
Hi
one way:
=SUMPRODUCT(--((J1:J100="reject")+(M1:M100="reject")+
(P1:P100="reject)
0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Karen" schrieb im
Newsbeitrag
...
I have a log that keeps track of rejects.
In one row the word "Reject" can appear in 3 columns
(Column J, M or P)of any combination.
Example: Row 5 can have "reject" in column P.
Row 6 can have "reject" in column J or M.
Row 7 can have "reject" in column J, M & P.
If "reject" appears in any one of these columns, I want
to return the value of 1. So, at the bottom of the
report, I want to count each time the word "reject"
appears in that row. Even though it appears once,
twice
or three times, I want it counted once.

Any suggestions?
Thank you and Happy Holidays

.

Hi
post the formula you have tried

--
Regards
Frank Kabel
Frankfurt, Germany

"Karen" schrieb im Newsbeitrag
...
I used your formula and adjusted the it with my ranges
and I'm getting the error #VALUE! Any suggestions?
Would it be easier to say that the range is J3:P57
instead of using the individual columns?
-----Original Message-----
Hi
one way:
=SUMPRODUCT(--((J1:J100="reject")+(M1:M100="reject")+
(P1:P100="reject)
0))

--
Regards
Frank Kabel
Frankfurt, Germany

"Karen" schrieb im
Newsbeitrag
...
I have a log that keeps track of rejects.
In one row the word "Reject" can appear in 3 columns
(Column J, M or P)of any combination.
Example: Row 5 can have "reject" in column P.
Row 6 can have "reject" in column J or M.
Row 7 can have "reject" in column J, M & P.
If "reject" appears in any one of these columns, I want
to return the value of 1. So, at the bottom of the
report, I want to count each time the word "reject"
appears in that row. Even though it appears once,
twice
or three times, I want it counted once.

Any suggestions?
Thank you and Happy Holidays

.