SUMIF within a range

A

Alonso

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

Pete_UK

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
 
A

Alonso

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
 
P

Pete_UK

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
 
T

T. Valko

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

Alonso

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

Pete_UK

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

Pete
 
A

Alonso

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
 
Top