Forum Discussion
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, 1...
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.
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. lol
=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
Ah 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.
The help provided earlier was based on the formulas and the description you provided in the first post.
If that doesn't make sense to you, you must seriously consider uploading a sample workbook with max 25 rows of dummy data with the desired output mocked up manually to show us what exactly you are trying to achieve.
