Count only rows meeting case-sensitive criteria and if adjacent row is not blank

Question

I'm trying to count cells within column A of Sheet 1 containing a case-sensitive data and if column B in same sheet is not blank. Currently, I'm able to count the case-sensitive cells using the formula below:

=IFERROR(IF($C2="","",COUNT(INDEX(FIND("mMMm",Sheet1!$A:$A),))),"-")

Next criteria to incorporate (besides the one above) would be to count only the values if its adjacent cell in column B is not blank. So far, I've tried incorporating a MATCH(<>"",Sheet1!B:B,0)* and an IF statement to achieve this, but to no avail. I also tried returning it as an array formula, but still nothing. May I ask for your guidance on this. I'm not sure if what I've done are actually on track to what I want to do, but I would love to know and learn a function that does the job. Thanks!

Here's some sample data to better grasp this concern:

Column A       Column B
12mMMmqq          1
sdmMmMqqq         1
ssmMMmree         1
wemMMmm12
mMMm11111
mMmMmM1121        1

So in the above sample data, my current formula would return a count of 4 (cells containing case-sensitive "mMMm"). However, what I wish to achieve is a count of 2 (those meeting the previous criteria and with a non-blank cell adjacent to it).

Thanks!

This can be done very easily with VBA. Would you like to try it out? :) — DirtyDeffy

QHarr QHarr · Accepted Answer · 2018-06-21T08:22:47

EDIT:

From your explanation in the comments, use:

=SUMPRODUCT(--(INDIRECT(C1&"!F2:F7")<>""),--(ISNUMBER(FIND("mMMm",INDIRECT(C1&"!A2:A7")))))

This assumes C2 in current sheet has the sheet name e.g. Sheet1. And that column A and column F of that sheet are those of interest. You would adjust the ranges ensuring they are the same length

You can use

=SUMPRODUCT(--(B2:B7<>""),--(ISNUMBER(FIND("mMMm",A2:A7,1))))

If in another sheet then add the sheet name in front of the range e.g.

=SUMPRODUCT(--(B2:B7<>""),--(ISNUMBER(FIND("mMMm",Sheet1!A2:A7,1))))

If wanting blanks in col B then use =""

Same sheet example:

Notes:

Find, unlike Search is case sensitive.

You can use SUMPRODUCT to handle the two arrays. Array 1 is a boolean array of column B <> "", array 2 is column A where Find has found "mMMm" and therefore returned a number. This is wrapped in ISNUMBER generating a boolean array.

Both boolean arrays are converted to their respective 1 or 0 using --.

Count only rows meeting case-sensitive criteria and if adjacent row is not blank

1 Answers