Identifying numbers in colum

[Kev Smith]

I need to be able to identify a number from 1 to 11 in a column and say for instance I identify 1 i then need to be able to use another number from a different colum in the same row. ie If there were 10 number 1s in the column I want to be able to add the 10 cooresponding numbers from a different column so they can be used in a chart.

 

[Peter_SSs]

Kev Smith

Welcome to the Mr Excel board!

Could you give a small sample of data from the two columns and the desired result for these rows?

 

[Kev Smith]

The numbers 1 to 11 represent fault codes for products, the number I need to use in the chart are the number of faults associated with the fault code. these are compiled on a monthly basis.

 

[Richard Schollar]

Kevin

You can use SUMIF for this:

I am assuming that your numbers 1 to 11 are in column 'A' and the associated numbers are in column 'D' (for argument's sake) then:

=SUMIF(A1:A100,1,D1:D100)

would look at the range in A1:A100 and for those with a value of 1 in them, will sum the corresponding numbers in D1:D100. You can amend the ranges to suit. Also, you could have numbers 1 to 11 listed in cells somewhere on your worksheet (like E1:E11) in which case you could apply the following formula in F1 say and copy down:

=SUMIF(A$1:A$100,E1,D$1:D$100)

Hope this helps!

Richard

 

[Kev Smith]

Thanks for the info. Would this work if I wanted to add another variable? ie I have seven sites and I need these to be independant the sites are identified by text ie BH, Con, etc.

I want to be able to look at the 11 faults from each site all the info is in 1 spreadsheet.

 

[Richard Schollar]

Yes, by using another function (SUMPRODUCT). Extending my previous example, say column B then had the different sites:

=SUMPRODUCT(--(A1:A100=5),--(B1:B100="BH"),D1:D100)

The way to read this formula is:

the A1:A100 bit is converted to an array (or list) 100 elements long of 0 and 1 values depending on whether the cells contain the value 5 (you can replace this 5 with another fault value or with a cell reference like E2 say to suit), 0 if it doesn't, 1 if it does. The B1:B100 bit does exactly the same thing based on the cells containing "BH" (adjusted to suit). The respective elements of these two arrays (1s and 0s) are then multiplied with the corresponding element in D1:D100 and summed together. The double minus -- is required before the first two arrays, as the 100 elements are actually returned as TRUE/FALSE values, and the -- coerces Excel to convert these to 1s and 0s.

Hope this helped!

Richard

 

[Kev Smith]

Identifying numbers

I have another problem with identifying numbers, once I have found and entered the values for say november. I need this data to stay in the cells for nov. even though the fault list has changed to Decembers I need the history of the november numbers for the charts. I know how to get the data for Dec but it is the retention of the Nov dta even if the source has gone.

Many thanks in anticipation.