Forum Discussion

SpaceCityCowboy88's avatar
SpaceCityCowboy88
Copper Contributor
Nov 25, 2019

Count cells with multiple criteria like COUNTIFS() but still get the functionality of case sens.??

How can I count cells with multiple criteria like COUNTIFS() but still get the functionality of case sensitivity?

 

In other words I need something like this:

 

=COUNTIFS($C:$C,"<"&DATE($AB$5, 11, 1), https://twitter.com/search?q=%24C&src=cashtag_click:$C,">="&DATE($AB$5, 10, 1), DataReport!$E:$E, "*"&"LA"&"*")

 

=COUNTIFS($C:$C,"<"&DATE($AB$5, 11, 1), https://twitter.com/search?q=%24C&src=cashtag_click:$C,">="&DATE($AB$5, 10, 1), DataReport!$E:$E, "*"&"La"&"*")

 

https://twitter.com/search?q=%24AB&src=cashtag_click$5 references a cell that simple states the year 2019.

 

Notice that I want two different counts here LA and La within the month of Oct 2019.

 

If I could find a way to make this case sensitive then I would work perfectly. How would I go about doing something like this?

 

Thanks in advance.

excel
Excel Functions

7 Replies

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Nov 25, 2019

    SpaceCityCowboy88 

    Do you think your date criteria are correct?

    You are comparing a date column against a single date so the dates in the date column would either be less than or equal to or greater than the criteria date.

     

    As far as counting based on case sensitive is concerned, you may use FIND function like this...

     

    =SUMPRODUCT((C1:C100<=AB5)*(ISNUMBER(FIND("LA",DataReport!E1:E100))))

    Adjust the range reference and the date criteria as per your requirement.

     

    But don't use whole column reference in the formula rather limit it to the max possible rows.

    • SpaceCityCowboy88's avatar
      SpaceCityCowboy88
      Copper Contributor
      Nov 25, 2019
      I see, one more question though. This function says take everycell that has a date greater than or equal to and times it by the number of cells that contain the keyword.

      This is closer to what I need but still isn't it.

      I need a function that will count rows that are within a date range and also contain a specific case sensitive keyword.

      pseudo code:

      year = (a cell containing the year "2019" so I can dynamically update all my functions as the years change)
      date = (array of cells containing dates)
      label = (array of cells to search for the keyword case sensitive)
      count = 0

      for (range of rows){
      if (date >= "10/01/"+year){
      if (date <= "10/31/"+year){
      if (label == "La")
      count++
      }
      }
      }
      }
      return count;

      something like that.

      • SpaceCityCowboy88's avatar
        SpaceCityCowboy88
        Copper Contributor
        Nov 25, 2019
        Basically I want to count rows that fall withing a date range and have a certain case sensitive keyword. Seem like it should be simple, but COUNTIFS() isn't case sensitive which seems like would be a common problem. There has to be a common fix. Sum product counts one specific thing. for instance

        =IMSUB(SUMPRODUCT((H102:H119>=DATE(2019,10,1))*1), SUMPRODUCT((H102:H119>DATE(2019,11,1))*1))

        This found every date greater than or equal to October 1st and found every date greater than November 1st then subtracted the difference. This gave me an accurate count of days between desired date range of October. Now how many of those days contain a specific keyword on the same row as those days. Don't get the addresses of the rows returned so I can't actually check those rows for any other criteria like in COUNTIFS(). Why can't the thing just be case sensitive. lol

Resources