SUMIF within a range

Hi Pete

my mistake
I didn't explain myself

the problem is that in column A
I´ve a list of machines (M1, M2, and so on)
Column U to AB are hours - to check if the machine is working

what I want to do is verify if the machine is working at anytime within the
range
(1) or not (0)
thus, I'm making a search (ie: M1), look into the list (column A), check for
activity (numbers in column U to AB) and return a 1 if found it

it works when the numers are in the first column
if not, return (0) althought there are numbers on the other columns


hope its clear now


Alonso




"Pete_UK" wrote:

> Do you mean that you want to check row 5 - if any of the numbers in
> U5:AB5 are equal to A5 then return a 1? And do this for the rows 5 to
> 94? If so, put this formula in, say, AC5:
>
> =IF(COUNTIF(U5:AB5,A5)>0,1,0)
>
> Then you can copy this down to AC94 to get a series of 1s and 0s
>
> Hope this helps.
>
> Pete
>
> On Sep 4, 9:51 pm, Alonso <Alo....RemoveThis@discussions.microsoft.com> wrote:
> > i'm trying to check within a range (u5:ab94) if there are values
> > based on the content in column A
> > to identify if there is activity
> > but it only return a 1 (used) if the numbers are in the first column (in
> > this case:
> > U)
> >
> > -the problem is that i have values in columns W, X, etc
> > and it still returns a 0 (thus, saying NO action)
> >
> > ie:
> > =IF(SUMIF(Programa!$A$5:$A$94,$M6,Programa!U5:AB94)>0,1,0)
>
>

I'm still unclear. Do you have something like this:

A U V W X Y Z AA AB
5 M1 0 1 1 0 1 1 1 1
6 M2 1 1 1 1 0 1 1
1
7 M3 1 0 1 1 1 1 1 0
8 M4 1 1 0 0 0 0 1 1

and so on down to row 94?

And so you want to check that there is at least one 1 on any row in
order to confirm that the machine was operational that day? You want
to show this with another 1? If so, where? - do you want to show this
on each row, or do you want to use another cell (which one?) where you
can enter the machine and have a 1 or a 0 in the next cell?

Please describe what you want to achieve in more detail.

Pete


On Sep 4, 11:41 pm, Alonso <Alo....DeleteThis@discussions.microsoft.com> wrote:
> Hi Pete
>
> my mistake
> I didn't explain myself
>
> the problem is that in column A
> I´ve a list of machines (M1, M2, and so on)
> Column U to AB are hours - to check if the machine is working
>
> what I want to do is verify if the machine is working at anytime within the
> range
> (1) or not (0)
> thus, I'm making a search (ie: M1), look into the list (column A), check for
> activity (numbers in column U to AB) and return a 1 if found it
>
> it works when the numers are in the first column
> if not, return (0) althought there are numbers on the other columns
>
> hope its clear now
>
> Alonso
>
>
>
> "Pete_UK" wrote:
> > Do you mean that you want to check row 5 - if any of the numbers in
> > U5:AB5 are equal to A5 then return a 1? And do this for the rows 5 to
> > 94? If so, put this formula in, say, AC5:
>
> > =IF(COUNTIF(U5:AB5,A5)>0,1,0)
>
> > Then you can copy this down to AC94 to get a series of 1s and 0s
>
> > Hope this helps.
>
> > Pete
>
> > On Sep 4, 9:51 pm, Alonso <Alo....DeleteThis@discussions.microsoft.com> wrote:
> > > i'm trying to check within a range (u5:ab94) if there are values
> > > based on the content in column A
> > > to identify if there is activity
> > > but it only return a 1 (used) if the numbers are in the first column (in
> > > this case:
> > > U)
>
> > > -the problem is that i have values in columns W, X, etc
> > > and it still returns a 0 (thus, saying NO action)
>
> > > ie:
> > > =IF(SUMIF(Programa!$A$5:$A$94,$M6,Programa!U5:AB94)>0,1,0)- Hide quoted text -
>
> - Show quoted text -

Very close Pete

I have diferent numbers in the rows
(items completed)

Machines could appear more than once on the A column
ie
A U (8:00) V(9:00) W(10:00) Y(11:00) Z(12:00)
5 M1 20 10
6 M2 - 5 5 10

7 M3 - - - -
-
....
....
11 M1 - - - 20


On another table I search for each machine
to find if the machine was working
thus, this should show something like this:
M1 1 (working)
M2 1 (working)
M3 0 (idle)





"Pete_UK" wrote:

> I'm still unclear. Do you have something like this:
>
> A U V W X Y Z AA AB
> 5 M1 0 1 1 0 1 1 1 1
> 6 M2 1 1 1 1 0 1 1 1
> 7 M3 1 0 1 1 1 1 1 0
> 8 M4 1 1 0 0 0 0 1 1
>
> and so on down to row 94?
>
> And so you want to check that there is at least one 1 on any row in
> order to confirm that the machine was operational that day? You want
> to show this with another 1? If so, where? - do you want to show this
> on each row, or do you want to use another cell (which one?) where you
> can enter the machine and have a 1 or a 0 in the next cell?
>
> Please describe what you want to achieve in more detail.
>
> Pete
>
>
> On Sep 4, 11:41 pm, Alonso <Alo....TakeThisOut@discussions.microsoft.com> wrote:
> > Hi Pete
> >
> > my mistake
> > I didn't explain myself
> >
> > the problem is that in column A
> > I´ve a list of machines (M1, M2, and so on)
> > Column U to AB are hours - to check if the machine is working
> >
> > what I want to do is verify if the machine is working at anytime within the
> > range
> > (1) or not (0)
> > thus, I'm making a search (ie: M1), look into the list (column A), check for
> > activity (numbers in column U to AB) and return a 1 if found it
> >
> > it works when the numers are in the first column
> > if not, return (0) althought there are numbers on the other columns
> >
> > hope its clear now
> >
> > Alonso
> >
> >
> >
> > "Pete_UK" wrote:
> > > Do you mean that you want to check row 5 - if any of the numbers in
> > > U5:AB5 are equal to A5 then return a 1? And do this for the rows 5 to
> > > 94? If so, put this formula in, say, AC5:
> >
> > > =IF(COUNTIF(U5:AB5,A5)>0,1,0)
> >
> > > Then you can copy this down to AC94 to get a series of 1s and 0s
> >
> > > Hope this helps.
> >
> > > Pete
> >
> > > On Sep 4, 9:51 pm, Alonso <Alo....TakeThisOut@discussions.microsoft.com> wrote:
> > > > i'm trying to check within a range (u5:ab94) if there are values
> > > > based on the content in column A
> > > > to identify if there is activity
> > > > but it only return a 1 (used) if the numbers are in the first column (in
> > > > this case:
> > > > U)
> >
> > > > -the problem is that i have values in columns W, X, etc
> > > > and it still returns a 0 (thus, saying NO action)
> >
> > > > ie:
> > > > =IF(SUMIF(Programa!$A$5:$A$94,$M6,Programa!U5:AB94)>0,1,0)- Hide quoted text -
> >
> > - Show quoted text -
>
>

Did you try out Biff's solution? You will need to adjust the ranges to
suit your data.

Pete

On Sep 5, 12:46 am, Alonso <Alo... DeleteThis @discussions.microsoft.com> wrote:
> Very close Pete
>
> I have diferent numbers in the rows
> (items completed)
>
> Machines could appear more than once on the A column
> ie
>       A          U (8:00)   V(9:00)   W(10:00)   Y(11:00)   Z(12:00)
> 5   M1          20              10              
> 6   M2          -                  5              5                 10      
>
> 7   M3          -                 -                 -                 -      
>            -
> ...
> ...
> 11 M1          -                  -              -                      20
>
> On another table I search for each machine
> to find if the machine was working
> thus, this should show something like this:
> M1   1   (working)
> M2   1   (working)
> M3   0   (idle)
>
>

> Column U to AB are hours

Assuming there are no TEXT entries in that range.

Try something like this:

=--(SUMPRODUCT((A1:A10="M1")*U1:AB10)>0)

--
Biff
Microsoft Excel MVP


"Alonso" <Alonso RemoveThis @discussions.microsoft.com> wrote in message
news:05E098B8-A8C9-4074-90DA-4D39CB421DDD@microsoft.com...
> Hi Pete
>
> my mistake
> I didn't explain myself
>
> the problem is that in column A
> I´ve a list of machines (M1, M2, and so on)
> Column U to AB are hours - to check if the machine is working
>
> what I want to do is verify if the machine is working at anytime within
> the
> range
> (1) or not (0)
> thus, I'm making a search (ie: M1), look into the list (column A), check
> for
> activity (numbers in column U to AB) and return a 1 if found it
>
> it works when the numers are in the first column
> if not, return (0) althought there are numbers on the other columns
>
>
> hope its clear now
>
>
> Alonso
>
>
>
>
> "Pete_UK" wrote:
>
>> Do you mean that you want to check row 5 - if any of the numbers in
>> U5:AB5 are equal to A5 then return a 1? And do this for the rows 5 to
>> 94? If so, put this formula in, say, AC5:
>>
>> =IF(COUNTIF(U5:AB5,A5)>0,1,0)
>>
>> Then you can copy this down to AC94 to get a series of 1s and 0s
>>
>> Hope this helps.
>>
>> Pete
>>
>> On Sep 4, 9:51 pm, Alonso <Alo... RemoveThis @discussions.microsoft.com> wrote:
>> > i'm trying to check within a range (u5:ab94) if there are values
>> > based on the content in column A
>> > to identify if there is activity
>> > but it only return a 1 (used) if the numbers are in the first column
>> > (in
>> > this case:
>> > U)
>> >
>> > -the problem is that i have values in columns W, X, etc
>> > and it still returns a 0 (thus, saying NO action)
>> >
>> > ie:
>> > =IF(SUMIF(Programa!$A$5:$A$94,$M6,Programa!U5:AB94)>0,1,0)
>>
>>

Thanks Pete
I'll try Biff's
and return with feedback


"Pete_UK" wrote:

> Did you try out Biff's solution? You will need to adjust the ranges to
> suit your data.
>
> Pete
>
> On Sep 5, 12:46 am, Alonso <Alo....DeleteThis@discussions.microsoft.com> wrote:
> > Very close Pete
> >
> > I have diferent numbers in the rows
> > (items completed)
> >
> > Machines could appear more than once on the A column
> > ie
> > A U (8:00) V(9:00) W(10:00) Y(11:00) Z(12:00)
> > 5 M1 20 10
> > 6 M2 - 5 5 10
> >
> > 7 M3 - - - -
> > -
> > ...
> > ...
> > 11 M1 - - - 20
> >
> > On another table I search for each machine
> > to find if the machine was working
> > thus, this should show something like this:
> > M1 1 (working)
> > M2 1 (working)
> > M3 0 (idle)
> >
> >
>