Help with a formula

[leemcder]

Hi, can someone please help with a formula please? The top formula works fine. The formula below it is identical apart from it should count cells in the data sheet, column U, where it contains No or is blank. The top formula counts where the cells contains Yes. Can someone please point out why it doesn't not count the No and blank cells? Many thanks

=SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900"​

,Data!U:U,"Yes​

",Data!N:N,"",Data!F:F,{"Clinical Negligence","Financial Mis-Selling","Highway Tripper","Holiday Illness","Industrial Disease","Other PI (Non RTA)","SIPP","Solar","Tripping","Works Accident"})) This works

=SUM(COUNTIFS(Data!R:R,RTA!A:A,Data!Q:Q,">1/1/1900",​

​

Data!U:U,{"No",""}​

​

,Data!N:N,"",Data!F:F,{"Clinical Negligence","Financial Mis-Selling","Highway Tripper","Holiday Illness","Industrial Disease","Other PI (Non RTA)","SIPP","Solar","Tripping","Works Accident"}))​

_{^{<strike>
</strike>}}​

 

[Fluff]

Cross posted https://www.excelforum.com/excel-fo...01-formula-to-countif-no-and-blank-cells.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[jim may]

All I can tell you is that the Countifs function does not allow strict arrays [ ] in their criteria ranges.

 

[Fluff]

As long as the Countifs is inside a sum, you can use an array with {}

 

[leemcder]

As long as the Countifs is inside a sum, you can use an array with {}

Thanks guys, as simple change was pointed out to me.

change from{"No",""} to

{"No";""}​

​

<strike></strike>

 

[jim may]

Thanks Fluff. I obviously quit reading the instructions I had found -- too soon...

Jim