Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions | |||
How to resize a comment box, by embedding code into a function? | Excel Worksheet Functions | |||
change function variable prompts?? | Excel Worksheet Functions | |||
Counting Function Dilemma | Excel Worksheet Functions |