SUMIF within a range

  • Thread starter Thread starter Alonso
  • Start date Start date
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)
 
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
 
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
 
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
 
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)
 
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)
 
Did you try out Biff's solution? You will need to adjust the ranges to
suit your data.

Pete
 
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
 
Back
Top