Help with formula pulling counts from a Table

[mark hansen]

I've been working on a formula for 3 days now and still getting a #VALUE error.
I have a table and need to count the amount of "Yes" and "No" answers in a range of columns.

I can count the amount of "Yes" (and "no") answers in a column range. This formula works
=COUNTIFS(Table1[[Was pain re-assessed and documented within 1 hour of intervention? ]:[Was falls risk assessment completed using age appropriate scale?]],"Yes")

I can count the amount of rows within a date range. This formula also works... (DB35 is start date and BD36 is ending date)
=COUNTIFS(Table1[Date of Visit],">="&BD35,Table1[Date of Visit],"<="&BD36)

But when I put them together I get a #VALUE error
=COUNTIFS(Table1[Date of Visit],">="&BD35,Table1[Date of Visit],"<="&BD36,Table1[[Was pain re-assessed and documented within 1 hour of intervention? ]:[Was falls risk assessment completed using age appropriate scale?]],"Yes")

What am I overlooking?

Thanks,
Mark

 

[mark hansen]

I forgot to add I can get a count of one column within a date range with this formula
=COUNTIFS(Table1[Date of Visit],">="&BD35,Table1[Date of Visit],"<="&BD36,Table1[Did the RN accomplish and document a Suicidal Ideation/Homicidal Ideation (SI/HI) risk assessment per local policy?],"no")

But when I try a column range, it doesn't work... for the life of me, I can't figure out why.
Any ideas?
Mark

 

[Fluff]

The ranges all need to be the same size, have a look at using SUMPRODUCT instead.

 

[etaf]

what are the actual ranges are they the same

 

[mark hansen]

The ranges are from a table, so the lengths are same. The Date of Visit is one column.

The range Table1[[Was pain re-assessed and documented within 1 hour of intervention? ]:[Was falls risk assessment completed using age appropriate scale?]],"
Is multiple columns wide.

I've been researching with SUMPRODUCT AND tried this so far...
Range (this works)
=SUMPRODUCT(--(Table1[[Was pain re-assessed and documented within 1 hour of intervention? ]:[Was falls risk assessment completed using age appropriate scale?]]="Yes"))

Date (this works
=SUMPRODUCT(--(Table1[Date of Visit]>=BD35),--(Table1[Date of Visit]<=BD36))

Combined (does not work)
=SUMPRODUCT(--(Table1[[Was pain re-assessed and documented within 1 hour of intervention? ]:[Was falls risk assessment completed using age appropriate scale?]]="Yes"),--(Table1[Date of Visit]>=BD35),--(Table1[Date of Visit]<=BD36))
The #VALUE error says the value used is the wrong data type

Still doing more research. maybe is the same array size. Don't know how to get around the date is in one column, but counting "Yes" in a range of columns.

found something that kept everything in one array..
=SUMPRODUCT(--(Table1[Date of Visit]>=BD35)+--(Table1[Date of Visit]<=BD36)+--(Table1[[Was pain re-assessed and documented within 1 hour of intervention? ]:[Was falls risk assessment completed using age appropriate scale?]]="Yes"))

It did provide a number, but it's way off

Mark

 

[Fluff]

Try it like

=SUMPRODUCT((Table1[Date of Visit]>=BD35)*(Table1[Date of Visit]<=BD36)*(Table1[[Was pain re-assessed and documented within 1 hour of intervention? ]:[Was falls risk assessment completed using age appropriate scale?]]="Yes"))

 

[mark hansen]

Thanks!!! that worked so far, (more testing needed) I don't understand the multiplication thing... but it works!

 

[Fluff]

You're welcome & thanks for the feedback.