+ Reply to Thread
Results 1 to 2 of 2

Combining a Date Range COUNTIF and a general COUNTIF

  1. #1
    Registered User
    Join Date
    09-15-2011
    Location
    Ny, NY
    MS-Off Ver
    Excel 2003
    Posts
    1

    Combining a Date Range COUNTIF and a general COUNTIF

    Hi -

    I have a column in my chart that has a set of dates and to determine if a date falls within a range located in A2 and A3 I use the following:
    =-COUNT(G43:G999)+COUNTIF(G43:G999,">="&A2)+COUNTIF(G43:G999,"<="&A3)

    I have a second column with names of companies which I total as follows:
    =COUNTIF(E43:E999,"*ALO*")

    Is there a way for me to combine both of these so that I can know how many times the ALO company listed in column E appears within a specified date range from column G?

    Thanks

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Combining a Date Range COUNTIF and a general COUNTIF

    Hello,

    in Excel 2007 and later, you can use Countifs() instead of Countif.

    =COUNTIFS(G43:G999,">="&A2,G43:G999,"<="&A3,E43:E999,"*ALO*")

    In Excel 2003 and earlier, Sumproduct() will be able to do what you need

    =SUMPRODUCT(--(G43:G999>=A2),--(G43:G999<=A3),--(NOT(ISERROR(SEARCH("*ALO*",E43:E999)))))

    Sumproduct does not support wildcards in comparisons, hence the loop through Search and Iserror.

    cheers,

+ 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