Results 1 to 6 of 6

COUNTIF using formula based on ROW and COLUMN

This is a discussion on COUNTIF using formula based on ROW and COLUMN within the Excel Questions forums, part of the Question Forums category; As part of a mathematical exploration, I have made a square set of cells which show "A", "B", and "C" ...

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    29

    Default

    As part of a mathematical exploration, I have made a square set of cells which show "A", "B", and "C" according to how each of the cell's row and column numbers are related to a third number (which is entered by the user). I won't go into detail about the math that determines if a cell shows A, B, or C because I have no trouble there.

    I want to make formulas that don't just simply count how many cells in a range have A, B, or C (I know how to do that). Rather, I want to make, for example, a formula that counts how many A cells there are in a range, but only counts the A cells for the cells whose column number divided by row number is a result less than 1. (I also want formulas to count such cells with results equal to 1 and greater than 1.)

    If I used COUNTIF($A$1:$J$10,AND("A",COLUMN()/ROW()<1)), the result is zero because the column and row functions are referencing the cell that the COUNTIF function is in rather than each of the cells in the desired range. Because the column and row functions only refer to the cell that the COUNTIF function is in, I can't count the cells I want.

    Any formula ideas?

  2. #2
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    something like this?

    =SUMPRODUCT(((COLUMN(A1:C3)/ROW(A1:C3))<1)*(A1:C3="a"))

    paddy

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    29

    Default

    Based on a little testing I did, I think that might work, although I am not familiar with how SUMPRODUCT operates so I am clueless right now about how it works, if it does work.

    I'll now mention complications that I left out earlier: I am actually basing the comparison on (ROW()+1)/2 rather than just ROW(), because I have every second row of cells with other information that doesn't affect the counts I want to do anyway. (Also, I am using letters other than "A", "B", and "C", which I put here for clarity.)

    So, let's see if I understand some of these parts so I can modify it at will:
    =SUMPRODUCT(((COLUMN(A1:C3)/ROW(A1:C3))<1)*(A1:C3="a"))

    If I wanted to modify that formula to count the cells in the range of A1 to F11 whereby the counted cells contain the text "Y" and those cells have a column number and row number such that COLUMN()/((ROW()+1)/2))<1, then the formula would be:

    =SUMPRODUCT(((COLUMN($A$1:$F$11)/((ROW($A$1:$F$11)+1)/2))<1)*($A$1:$F$11="Y"))

    Would that be correct?

    (I put $ in so I could copy the formula to other cells and modify the formula without affecting the specific range of cells considered for counting.)

  4. #4
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    =SUMPRODUCT(((COLUMN($A$1:$F$11)/((ROW($A$1:$F$11)+1)/2))<1)*($A$1:$F$11="Y"))


    Looks right to me. for details on what sumproduct's doing, see:

    http://mrexcel.com/board/viewtopic.p...=20900&forum=2

    Paddy

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,749

    Default

    On 2002-10-13 15:49, PaddyD wrote:
    =SUMPRODUCT(((COLUMN($A$1:$F$11)/((ROW($A$1:$F$11)+1)/2))<1)*($A$1:$F$11="Y"))


    Looks right to me. for details on what sumproduct's doing, see:

    http://mrexcel.com/board/viewtopic.p...=20900&forum=2

    Paddy

    Paddy,

    The SUMPRODUCT link is gone after the old board has been processed into a CD...

    http://www.mrexcel.com/search.shtml

    Aladin

  6. #6
    MrExcel MVP
    Join Date
    May 2002
    Posts
    13,993

    Default

    Bugger! Do you still have the text available? Prepared to post it up in a new thread so we can continue to refer to it?

    Paddy

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com