+ Reply to Thread
Results 1 to 5 of 5

Countif funtions problem

  1. #1
    Registered User
    Join Date
    06-11-2007
    Posts
    24

    Countif funtions problem

    I have the following Countif formula working but I wish to add many more columns to the function but excel says I can't do that. Is it possible or do I use another function? Thanks for all your help.

    Mike

    WORKS
    =COUNTIF(Q21:Q128:R21:R128,"1-*")

    DOES NOT WORK
    =COUNTIF(Q21:Q128:R21:R128:V21:V128:W21:W128:AA21:AA128:AB21:AB128:AF21:AF128:AF21:AG128:AG21:AK128:AK21:AL128:AL21:AP128:AP21:AQ128:AQ21:AU128:AU21:AV128:AV21:AZ128:AZ128:BA128:BA128:BE128:BE128:BF128:BF128:BJ128:BJ128:BK128:BK128,"1-*")

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Try

    =SUMPRODUCT((LEFT(Q21:BK128,2)="1-")*(MOD(COLUMN(Q21:BK128)-COLUMN(Q21),5)<2))

  3. #3
    Registered User
    Join Date
    06-11-2007
    Posts
    24

    Countif funtions problem

    Works great but I have 1 small problem. In the string you provided is it possible to change the 1-* to add another line that has a value of say 10 or 11?

    Thanks again for all your help.

    Mike

  4. #4
    Registered User
    Join Date
    06-11-2007
    Posts
    24

    Sumproduct cell location problem + added problem

    I'm using the formula below to find "1-*" value but I would also like to determine the exact cell location and the result to show up in cell c8. The formula below is located in cell d8 if that matters. Also, I also use 10, 11, 12-* but the formula doesn't solve those figures. Thanks again for any help.

    Thanks for your help.

    Mike

    =SUMPRODUCT((LEFT(Q21:BK128,2)="1-")*(MOD(COLUMN(Q21:BK128)-COLUMN(Q21),5)<2))
    Last edited by mike4545; 06-17-2007 at 11:16 AM. Reason: adding on

  5. #5
    Registered User
    Join Date
    06-11-2007
    Posts
    24

    Sumproduct problem

    Below I have the following Sumproduct formula working but I wish to add ď10-* or 11-* to the function but it doesnít work. I started with the countif formula below. The problem was to increase the number of columns that was being counted. daddylonglegs solved the problem with the Sumproduct formula but Iím also needing is ď10-* or 11-* numbers to be searched.

    Also, I'm using the formula below to find "1-*or 1-10-*" value but I would also like to determine the exact cell location and the result to show up in cell c8. The formula below is located in cell d8 if that matters.

    Thanks for all your help.

    Mike

    Orginal problem

    =COUNTIF(Q21:Q128:R21:R128:V21:V128:W21:W128:AA21: AA128:AB21:AB128:AF21:AF128:AF21:AG128:AG21:AK128: AK21:AL128:AL21:AP128:AP21:AQ128:AQ21:AU128:AU21:A V128:AV21:AZ128:AZ128:BA128:BA128:BE128:BE128:BF12 8:BF128:BJ128:BJ128:BK128:BK128,"1-*")


    Below is what I'm using and it works

    =SUMPRODUCT((LEFT(Q21:BK128,2)="1-")*(MOD(COLUMN(Q21:BK128)-COLUMN(Q21),5)<2))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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