SUMIF within a range
 

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)

SUMIF within a range
 

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 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)

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 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)

SUMIF within a range
 

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 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 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 -

SUMIF within a range
 

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" wrote in message
...
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 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)

SUMIF within a range
 

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 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 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 -

SUMIF within a range
 

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 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)

SUMIF within a range
 

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 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)

SUMIF within a range
 

Hi Biff

I tried your solution
guess it's the right way
but I've errors

I check the calculation steps
it shows
#VALUE!s

perhaps it's because the isn't numbers in EVERY cell??
there are a lot of blanks




"T. Valko" wrote:

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" wrote in message
...
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 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)

SUMIF within a range
 

perhaps it's because the isn't numbers in EVERY cell??
there are a lot of blanks

Try this:

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


--
Biff
Microsoft Excel MVP


"Alonso" wrote in message
...
Hi Biff

I tried your solution
guess it's the right way
but I've errors

I check the calculation steps
it shows
#VALUE!s

perhaps it's because the isn't numbers in EVERY cell??
there are a lot of blanks




"T. Valko" wrote:

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" wrote in message
...
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 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)

SUMIF within a range
 

Thanks Biff!!
it worked perfect


"T. Valko" wrote:

perhaps it's because the isn't numbers in EVERY cell??
there are a lot of blanks

Try this:

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


--
Biff
Microsoft Excel MVP


"Alonso" wrote in message
...
Hi Biff

I tried your solution
guess it's the right way
but I've errors

I check the calculation steps
it shows
#VALUE!s

perhaps it's because the isn't numbers in EVERY cell??
there are a lot of blanks




"T. Valko" wrote:

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" wrote in message
...
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 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)

SUMIF within a range
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Alonso" wrote in message
...
Thanks Biff!!
it worked perfect


"T. Valko" wrote:

perhaps it's because the isn't numbers in EVERY cell??
there are a lot of blanks

Try this:

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


--
Biff
Microsoft Excel MVP


"Alonso" wrote in message
...
Hi Biff

I tried your solution
guess it's the right way
but I've errors

I check the calculation steps
it shows
#VALUE!s

perhaps it's because the isn't numbers in EVERY cell??
there are a lot of blanks




"T. Valko" wrote:

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" wrote in message
...
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 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)