Excel Formula help counting dynamicly?

Filed under: nnxj.com — webmaster @ March 15, 2010 edit
  • Ok so what I want to do is make a formula that will "evolve" if you will below is an example of what I'm looking at here. What I want to do is count how many cases each item number has. So basically I want to check the item number column and have it check the column to the right and count it and add it together then output the value to the right then once it finds a new item number it will output that quantity as well.

    ITEM NUMBER CASES
    43............................1
    11431.......................1
    11431.......................1
    12144...................... 1
    12888.......................1
    13816....................... 1
    58744...................... 1
    58884...................... 1


  • Assuming your source data is starting on a2 and c2, type the following..


    In cell ref c2:
    =COUNTIF(A$1:A2,A2)

    In cell ref d2:
    =IF(C2=1,+A2,"")

    In cell ref e2:
    =IF(D1="","",SUMIF(A:A,D2,B:B))

    Copy the formulaâ ™s down. This will summarise you source data, no duplicates and automatically "evolve" as you add new items.


  • you should make a pivot table out of this (i would need to know what version of excel you are using)
    but if you want to see the total number of items on the right, then just use the sumif() formula.

    - item number is column A
    - Cases is column B
    - in C1 label it Total Cases
    - in C2 copy&paste this formula

    =IF(A2="","",SUMIF(A:B,A2,B:B))

    - then copy&paste the formula down the column.

    - if you are ok with blanks in your total cases column. then this formula will leave a blank in column C.... then on the final instance of the duplicate from column A...it will give you your total.

    =IF(A2="","",IF(COUNTIF( A2:$A$999,A2)>1,"",SUMIF(A:B,A2,B:B)))

    copy&paste that down the column.
    - unlike the 1st formula, with this formula your total in column C should be equal to the total in coluimn B
    - copy the formula down up to 999 rows(add an extra 9 if you need more). when you add a new item in column A, the formula will automatically adjust for it.
    - you could also use auto-filter, and hide the blanks in column C. that will leave you with just a list of items, and the total amount.


    hope 1 of those works for you.







    • #If you have any other info about this subject , Please add it free.#
    Your name:
    E-mail:
    Telphone:

    Your comments:


    If you have any other info about Excel Formula help counting dynamicly? , Please add it free.