asked on

simple vba or excel formula

I want to be able to count different data corresponding to numbers in a column.
e.g - some basic data

SECTION PART #
1      abc505
1      abc505
1      abc303
1      abc303
1      abc123
1      abc505
1      abc505
2      abc505
2      abc505
2      abc303
2      abc303
2      abc123
2      abc505
2      abc505

i want a formula that will count how many abc505's i have if column A is section 1. so in this example i have 4 abc505's. the same goes for how many abc123's. id have, etc.
the same for section 2. in section 2 i want to know how many abc505's are in there. basically i need it to count the number of part numbers in each different section.
my out put might look something like this

SECTION PART# TOTAL
1 abc505 4
1 abc123 1
1 abc303 2
2 abc505 4
2 abc123 1
2 abc303 2

all i want is the total figure. i assume i will have a formula for each part number and section.
i may have up to 10 sections and 5 different part numbers.
i want this to be automatic though. i do not want to have to select the range of each section.
i am importing data and i may have 3 sections at one time or 10 another time all with different part numbers.
in my head i thought the formula would go thru the entire column sections at once checking for section 1 and counting the relevant part numbers, then checking for section 2 and counting the relevant part numbers and so on. like i said sometimes may have 3 sections another time 10 sections and i dont want to manually have to select the appropriate cell ranges for each section, etc.
maybe vb program would be necessary.

can someone help me with this please i only have basic excel formula knowledge
thankyou

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CSLARSEN

Hi

Select the range with your data.

Goto menuitem "Data"->"Pivottable...

Create a pivot table
1 .Use "excel range"

- place "SECTION" in the row section
-place PART# in both the row section and the data section.

Now you will have the count for each combination.

If you want to have a generic solution just select a very large "source" range.
E.g. select A1:B10000

Cheers
cslarsen

patrickab

rick81,

Here's yet another way to do it:

1. In column C put this formula in cell C2 and copy down the column to the end of the data:

=SUMPRODUCT(($A$2:$A$13=A2)*($B$2:$B$13=B2))

If the data is in 10,000 rows then make the formula:

=SUMPRODUCT(($A$2:$A$10000=A2)*($B$2:$B$10000=B2))

2. Select all the data in columns A and B only and go to Data/Filter/AdvancedFilter and tick the Unique Records box then press OK.

That will select all the unique records in columns A & B but the counts of those unique items will be visible in column C. To see all the records again go to Data/Filter 'Show All'.

Here's a file with it in:

http://my.storenow.net?f=1515

Hope that helps.

Patrick

patrickab

rick81,

This file has a slightly more relevant screen shot in it:

http://my.storenow.net?f=1516

Patrick

rick81

ASKER

Great! Both techniques work well. however there is one small question.
If i import new data into the same sheet. so the original data changes, e.g number of sections and part numbers will the pivot table automatically update?
Patrick i think your formula will be automatic.

byundt

One of the really nifty features of the PivotTable is the fact that it identifies the SECTION and Part # that are actually used in your data. While a SUMPRODUCT formula will automatically adjust to changes in data for the count or sum--it won't create that list of SECTION and Part # for you.

If you use a dynamic named range for the source of data in your PivotTable, then it will adjust as you import greater or lesser amounts of new data into your workbook. You would then just need to right-click the PivotTable and choose "Refresh data" from the resulting pop-up.

You may want to use dynamic named ranges for your chart or formula. These automatically expand and contract as you add or delete data. To create a dynamic named range:
1) Open the Insert...Name...Define menu item
2) In the "Names in workbook" field, enter a name like Dynamic1
3) In the "Refers to" field, enter a formula like:
=$A$2:INDEX($A$2:$A$100,COUNTA($A$2:$A$100)) Returns the range from A2 through the end of data in column A
4) Click "OK"

Note that Excel will add the worksheet name to the formula. The address range parts of the formula may refer to a specific range (as shown) or to an entire column such as COUNTA($A:$A). Finally, don't use relative cell addresses (without the $) because the formula almost certainly won't work as intended.

The dynamic named range formula will be thrown off track if there are any blanks within the data. A common workaround for this issue is to do the counting on a different column that ends in the same row, but doesn't contain any blanks.
=$B$2:INDEX($B$2:$B$100,COUNTA($A$2:$A$100)) Returns the range from B2 through the end of data (as defined by counting column A)

Another common formula for dynamic named ranges uses the OFFSET function. I don't like them as much because they are volatile (will recalc whenever any cell on workbook changes), thus slowing down recalc time.
=OFFSET($A$1,0,0,COUNTA($A$1:$A$100),1)

To use the dynamic named range as the source for your PivotTable, right-click the PivotTable and choose "PivotTable wizard" from the resulting pop-up. Hit the "Back" button until you reach step 2 of the wizard. Then type in the name of your dynamic named range, which should include both the headers labels and all columns of data you might want to use. Then click the "Finish" button.