Imbedding a COUNTIF function in an IF function across all rows in a sheet

[Orion19]

Hi!

I'm trying to count the number of times a phrase occurs across a sheet based on a qualifier in the row. The user enters a date and I want excel to first check the date in a row. If that date comes before (or is equal to) the date the user enters, excel should search the rest of the row for that phrase and count it. If the date falls after the user date any instance of the phrase in that row should be ignored. So if the user enters 1/2/18 and the search is for "BABB" the result should be 3 based on the table below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]User Date:[/TD]
[TD]1/2/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/1/18[/TD]
[TD]IMIT[/TD]
[TD]BABB[/TD]
[TD]BABB[/TD]
[TD]SPON[/TD]
[/TR]
[TR]
[TD]1/2/18[/TD]
[TD]BABB[/TD]
[TD]SPON[/TD]
[TD]SPON[/TD]
[TD]RESP[/TD]
[/TR]
[TR]
[TD]1/3/18[/TD]
[TD]BABB[/TD]
[TD]IMIT[/TD]
[TD]BABB[/TD]
[TD]BABB[/TD]
[/TR]
</tbody>[/TABLE]

This formula counts a single row, but I'm hoping to apply it to every row (I have a few thousand) and get a single number returned: =IF(A2 <= B1, COUNTIF(B2:E2, "BABB"), "0"). Any ideas how to apply that using a formula or VBA? Thank you!!!<style></style><style></style>

 

[Aladin Akyurek]

[TABLE="class: grid, width: 357"]
<tbody>[TR]
[TD]User Date:[/TD]
[TD="align: right"]1/2/2018[/TD]
[TD]BABB[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2018[/TD]
[TD]IMIT[/TD]
[TD]BABB[/TD]
[TD]BABB[/TD]
[TD]SPON[/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2018[/TD]
[TD]BABB[/TD]
[TD]SPON[/TD]
[TD]SPON[/TD]
[TD]RESP[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2018[/TD]
[TD]BABB[/TD]
[TD]IMIT[/TD]
[TD]BABB[/TD]
[TD]BABB[/TD]
[/TR]
</tbody>[/TABLE]

In D1 control+shift+enter, not just enter:

=SUM(IF(A2:A4<=B1,(B2:E4=C1)+0))

 

[Orion19]

Thank you!!! I'm so sorry for the late reply but I was trying to fix a problem I didn't know I initially had. Your suggestion totally worked, but I realized that the phrase I'm searching for can occur across multiple programs. I tried to modify the formula below (my addition is in bold) to include another qualifier but it returns "0" (definitely incorrect). Here's what I had:

=SUM(IF(AND(RawData!B2:B20000<=Dashboard!B2, RawData!E2:E20000="5 Minute Language Sample"), (RawData!Q2:DQ20000="BABB")+0))

I can get each individual component to work but not in a single formula. Thank you again for you help and any additional thoughts are appreciated!

 

[Aladin Akyurek]

AND does not work the way you hope in an array-processing formula. Try rather: Control+shift+enter, not just enter...

=SUM(IF(RawData!B2:B20000<=Dashboard!B2, IF(RawData!E2:E20000="5 Minute Language Sample", (RawData!Q2:DQ20000="BABB")+0)))​

 

[Orion19]

AND does not work the way you hope in an array-processing formula. Try rather: Control+shift+enter, not just enter...

=SUM(IF(RawData!B2:B20000<=Dashboard!B2, IF(RawData!E2:E20000="5 Minute Language Sample", (RawData!Q2:DQ20000="BABB")+0)))​

That did it! I didn't realize you can string multiple IF statements like that. Thank you so much!!!!!!<style></style>

 

[Aladin Akyurek]

That did it! I didn't realize you can string multiple IF statements like that. Thank you so much!!!!!!<style></style>

You are welcome. Thanks for the update.

 

[Orion19]

Hello again! I ran into another snag I can't seem to solve. One thing I'll need to look for is a date that falls between two dates. This formula will work where B2 is the cutoff date:

{=(AVERAGE(IF(RawData!$B$2:$B$15000<=Dashboard!$B$2, IF(RawData!$E$2:$E$15000="Facilitated Social Sessions Participation", IF(RawData!$F$2:$F$15000="Greetings: Reciprocates", (RawData!$P$2:$P$15000)))))}

But if I try to add another IF statement to limit the date range I get an error (#N/A). I'm trying to isolate rows that are on or before the date in C2 AND after the date in B2. There is data in the data set that matches these qualifications:

{=AVERAGE(IF(RawData!$B$2:$B$15000<=Dashboard!$C$2, IF(RawData!$B$2:$B$150000>Dashboard!$B$2, IF(RawData!$E$2:$E$15000="Facilitated Social Sessions Participation", IF(RawData!$F$2:$F$15000="Greetings: Reciprocates", (RawData!$P$2:$P$15000))))))}

Any ideas? I know formulas operate differently in an array but I'm not sure clear how. Thank you in advance!

 

[Aladin Akyurek]

Change RawData!$B$2:$B$150000 to RawData!$B$2:$B$15000.

 

[Orion19]

Change RawData!$B$2:$B$150000 to RawData!$B$2:$B$15000.

I caught that and fixed it. I was finally able to get a formula to work but when I attempt to record it in VBA it returns an error. Now this formula works when entered directly into a cell:

{=IFERROR(AVERAGE(IF(RawData!$B$2:$B$15000<=Dashboard!$C$2, IF(RawData!$B$2:$B$15000>Dashboard!$B$2, IF(RawData!$E$2:$E$15000="Facilitated Social Sessions Participation", IF(RawData!$F$2:$F$15000="Greetings: Reciprocates", (RawData!$P$2:$P$15000)))))), "NO DATA")}

But if I enter that while recording a macro the macro returns an error ("Unable to set the FormulaArray property of the Range class"). This is the code the macro recorder generates that is associated with the error:

Selection.FormulaArray = _
"=IFERROR(AVERAGE(IF(RawData!R2C2:R15000C2<=Dashboard!R2C3, IF(RawData!R2C2:R15000C2>Dashboard!R2C2, IF(RawData!R2C5:R15000C5=""Facilitated Social Sessions Participation"", IF(RawData!R2C6:R15000C6=""Greetings: Reciprocates"", (RawData!R2C16:R15000C16)))))), ""NO DATA"")"

I've been banging my head against the desk for hours trying to figure out what I'm missing. As always, thank you for any ideas!
<style></style>