#1   Report Post  
Karen
 
Posts: n/a
Default 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
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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


  #3   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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

  #4   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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

  #5   Report Post  
Karen
 
Posts: n/a
Default

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


.



  #6   Report Post  
Frank Kabel
 
Posts: n/a
Default

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


.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM
Find a Function to use accross different worksheets R. Hale Excel Worksheet Functions 3 November 25th 04 08:07 AM
How to resize a comment box, by embedding code into a function? JJ Excel Worksheet Functions 13 November 16th 04 09:44 PM
change function variable prompts?? thinkingfield Excel Worksheet Functions 1 November 8th 04 05:01 PM
Counting Function Dilemma Simon Lloyd Excel Worksheet Functions 0 November 8th 04 04:13 PM


All times are GMT +1. The time now is 06:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"