# Identifying numbers in colum

#### Kev Smith

##### New Member
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.

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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?

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.

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

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.

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

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.

Replies
12
Views
437
Replies
7
Views
128
Replies
2
Views
131
Replies
3
Views
137
Replies
18
Views
245

### Forum statistics

1,196,448
Messages
6,015,329
Members
441,889
Latest member
balolaptopgiaolong

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back