COUNTIF Multiple Criteria using ENTIRE column as selection

  • I searched through the threads related to countif with multiple criteria to find my answer and came up empty. Is it possible to have the selection be an entire column, A:A and K:K, in this example? Rather than a defined column/row range?


    =SUMPRODUCT(--('2005'!A$2:A$2344="January"),--('2005'!K$2:K$2344<575))


    When I choose the entire column the result is #NUM!. This formula works for my previous years data sheet, 2005, because no more data is added to it.


    However, 2006 will have data added to it on a daily basis and I don't want to have to change the selection each time to get the accurate results.


    Thanks in advance!

  • Re: COUNTIF Multiple Criteria using ENTIRE column as selection


    As far as I know you can't use whole columns with SUMPRODUCT.


    If the range is constantly changing why not use a dynamic named range.


    If you search the board I'm sure you'll find many examples.

  • Re: COUNTIF Multiple Criteria using ENTIRE column as selection


    Not exactly sure why array-style formulas in general are picky about the entire column references... Yeah, I know SUMPRODUCT is not an array-entered formula, but if you notice, it adheres to the same rules.


    I doubt you'd notice any difference if the SUMPRODUCT was forced to refer only to the first 65,535 rows. Seems very unlikely your application will come crashing down if you happen to exclude that 65,536th row.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: COUNTIF Multiple Criteria using ENTIRE column as selection


    Quote from norie

    As far as I know you can't use whole columns with SUMPRODUCT.


    If the range is constantly changing why not use a dynamic named range.


    If you search the board I'm sure you'll find many examples.


    I didn't even think of using a Named Range. This would actually work perfectly. The data set I'm using for any given month should never exceed 500 rows of data.


    I actually made this more difficult then it needed to be. Thanks for the help!

  • Re: COUNTIF Multiple Criteria using ENTIRE column as selection


    Quote from EgoProwler

    I didn't even think of using a Named Range. This would actually work perfectly. The data set I'm using for any given month should never exceed 500 rows of data.


    I actually made this more difficult then it needed to be. Thanks for the help!


    500 rows? Why even bother with the named range overhead? Ah well, it's you're app...

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: COUNTIF Multiple Criteria using ENTIRE column as selection


    Quote from Aaron Blood

    500 rows? Why even bother with the named range overhead? Ah well, it's you're app...


    Aaron, was my error. My old data set prior to this rebuild was the 500 range size. Currently, I've consolidated multiple sheets into one and there are over 3000 rows.


    This is personal use only and I know nothing about how the name range will effect overhead. I'm familiar with the concept, but not how it relates to Excel. However, you've peaked my interest and I'd like you to elaborate on this 'overhead' discussion.

  • Re: COUNTIF Multiple Criteria using ENTIRE column as selection


    Overhead in the sense that it's an ugly complicated mess of a formula... Also makes auditing/editing/maintenance more difficult. I personally don't find myself using them, ever. But that's just me.


    As opposed to just a static reference of say a nice round 10K, or even the 65535 would be preferrable. Avoiding complexity is always a plus in a living application.


    When I come out and say things like I hate named ranges in cell formulas and never use circular references... or I don't care for the fancy variable-size range names it tends to upset the populace. (You all know I don't like using too many STANDARD range names in formulas, how'd you think I'd feel about the expando-range names?)


    They're all facinating examples, and wonderfully clever, but there all basically future trainwrecks and I tend to avoid them. But that's me speaking from my background and experiences mostly in the business/finance/accounting world world.


    Hopefully this won't spark 30 replies now arguing the virtues of any or all of the above mentioned... I've responded to threads like this too often in the past, you all can google em up if you like or read my epistle on what I think are good spreadsheet design philosophies that I've posted to my website.


    Anyway... in the end, it's your application.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: COUNTIF Multiple Criteria using ENTIRE column as selection


    Aaron


    Are you saying your against dynamic named ranges?


    If you are I'm sort of with you, but the main reason for that is I've never quite got my head around them and have used other methods in the past.


    I'm not that hot on formula(e?).:).

  • Re: COUNTIF Multiple Criteria using ENTIRE column as selection


    Quote from Aaron Blood

    They're all facinating examples, and wonderfully clever, but there all basically future trainwrecks and I tend to avoid them. But that's me speaking from my background and experiences mostly in the business/finance/accounting world world.


    Aaron, thanks for the reply. We all know that there is more than one way to skin a cat and based on your practical experience -- skinning the cat the way suggested hasn't worked well when building complex applications. It makes perfect sense to me, thanks for clarifying your position.


    What I gather from your reply is using plain ole cell references rather than named ranges is plenty sufficient and named ranges is merely a fancy way of accomplishing the same result.

  • Re: COUNTIF Multiple Criteria using ENTIRE column as selection



    Oh... will I let myself get sucked in again...


    Not only am I against dynamic named ranges, I'm pretty much against using anymore than say, 10-15 regular named ranges in cell formulas thruout a spreadsheet.


    Really a good usage would be an anchor point for a macro. In which case I prequalify those with "VBA" so I know which ones they are: "VBA_MyName"


    I don't say "never" I just say "avoid" and prolific usage just results in headaches down the road. Fewer the better in my book.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!