Forum Discussion
Text with "*[text]*" with SUMPROD function
Hello !
I would like to look for how many subjects are late for each subdivision and by how much (more than 3 months, less than 6 months, and more than 6 months), but the raw data is an extraction from an electronic system so the formatting is quite awful.
So to look for a certain topic numbers, I need to use "*subdivision*" to be able to find the correct subdivision.
Some subdivision will also share the same subject, and I need to differentiate for each.
The thing is, it seems that looking for a part of a text isn't compatible with SUMPROD... Any idea how I can bypass this issue ? Or maybe with another function ?
I have put an sample file so it's easier to test.
Thank you in advance for any help !
You can use SUMIFS:
2 Replies
SUMPRODUCT works as well
=SUMPRODUCT( ($B$2:$B$23 = "Late") * ($D$2:$D$23 <= EDATE($H$1,-3) ) * ( $D$2:$D$23 > EDATE($H$1,-6) ) * ISNUMBER(SEARCH("\" & H$25, $C$2:$C$23)) )You can use SUMIFS:
