+ Reply to Thread
Results 1 to 10 of 10

Copy Count Formula and Have Criteria Increase by !

  1. #1
    Registered User
    Join Date
    07-11-2010
    Location
    Goddard, Kansas
    MS-Off Ver
    Office 365
    Posts
    21

    Copy Count Formula and Have Criteria Increase by !

    I have a workbook that I am counting the number of times a number occurs in the column. I would like to know how to copy the formula or create a formula that will increase the criteria by one.

    Example =COUNTIF(Table2,1)
    =COUNTIF(Table2,2)

    I would like to be able to have it do this when I copy the cell or use the crosshair to drag it down.

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Copy Count Formula and Have Criteria Increase by !

    Try

    =COUNTIF(Table2,Row(A1))

  3. #3
    Registered User
    Join Date
    07-11-2010
    Location
    Goddard, Kansas
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Copy Count Formula and Have Criteria Increase by !

    Did not do what I need it to do. I have attached the workbook so that you can see what I need.
    Attached Files Attached Files

  4. #4
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Copy Count Formula and Have Criteria Increase by !

    Works perfectly as far as I can see.

  5. #5
    Registered User
    Join Date
    07-11-2010
    Location
    Goddard, Kansas
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Copy Count Formula and Have Criteria Increase by !

    I am counting the number of times the #1, #2, #3, etc. occurs in the sheet "powerball" in column b

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Copy Count Formula and Have Criteria Increase by !

    if you mean that you want a count of the numbers you have in Sheet1 in col A without referring to that column, you could substitute this instead...
    COUNTIF(powerball!B:B,ROW(1:1))
    that will increment by 1 as it goes down.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  7. #7
    Registered User
    Join Date
    07-11-2010
    Location
    Goddard, Kansas
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Copy Count Formula and Have Criteria Increase by !

    I want to count on the powerball sheet the number of times the number 1 occurs in column b and have that listed on sheet1 in column b. Column b on sheet1 has the formula "=COUNTIF(Table2,1)". I want to have the next row on sheet1 to increment by one to read "=COUNTIF(Table2,2) to count the number of times the #2 occurs in column b of powerball and so on.

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Copy Count Formula and Have Criteria Increase by !

    the formula I gave you does that.
    This formula also does that...=COUNTIF(powerball!B:B,A2)
    And I suspect the formula Bob gave you likely works too.
    If these do not give you the answers you want, then please upload a different workbook WITH your expected results because I am not seeing how what you are explaining is different from what you have been given?

  9. #9
    Registered User
    Join Date
    07-11-2010
    Location
    Goddard, Kansas
    MS-Off Ver
    Office 365
    Posts
    21

    Re: Copy Count Formula and Have Criteria Increase by !

    Thanks it is working with the last reply. Was a little slow on the uptake on understanding the formula from both Bob and you.

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Copy Count Formula and Have Criteria Increase by !

    Ok, glad we could help, AND thank you for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Increase Formula row +1 in copy of sheet
    By Coral916 in forum Excel General
    Replies: 5
    Last Post: 05-16-2019, 08:32 AM
  2. [SOLVED] need a formula for increase % between criteria with my existing data
    By santosh226001 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-18-2017, 01:40 AM
  3. [SOLVED] Formula for incremental increase of count
    By BRISBANEBOB in forum Excel General
    Replies: 3
    Last Post: 09-15-2016, 09:21 AM
  4. copy sum formula across a row but increase columns
    By ammartino44 in forum Excel General
    Replies: 5
    Last Post: 09-01-2015, 07:20 PM
  5. [SOLVED] Copy formula across columns, but row number to increase
    By papasmurfuo9 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-26-2014, 10:27 AM
  6. [SOLVED] Need 'criteria' within AVERAGEIFS formula to automatically increase when dragging down
    By javboy800 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-07-2014, 10:49 AM
  7. Replies: 2
    Last Post: 04-17-2010, 10:52 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1