Forum Discussion
Count cells with multiple criteria like COUNTIFS() but still get the functionality of case sens.??
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.
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.
- 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. lolAh I see, I misunderstood the date criteria.
You may try something like this...
=SUMPRODUCT((C102:C119<DATE(AB5,11,1))*(C102:C119>=DATE(AB5,10,1))*(ISNUMBER(FIND("LA",DataReport!E102:E119))))
Of course change the ranges as per your requirement.
- This will multiply the number of dates greater than or equal to October 1st times the dates less than November 1st times the amount of times "LA" shows up in the entire sheet... This makes no sense. I want to find rows within a date range then, of those rows, count the ones that contain "LA" case sensitive. I don't know how else to explain it. Thank you for your help.