COUNTIFS with multiple array

[JCTalk]

Hi Guys,

I'm working on a COUNTIFS which previously worked fine as it only had one array in it, but now I have a requirement to add a second it's not calculating correctly. I read this is because the second array is being treated as an OR.

I've seen a SUMPRODUCT can be used instead, but I can't get it work with my data.

This is my COUNTIFS version with both array's which I know won't work. But can anyone help me convert this into something that will calculate correctly.

=SUM(COUNTIFS($E:$E,"19/07/2017",$P:$P,"<3",$B:$B,">14",$N:$N,{"Text1a","Text2a","Text3a","Text4a","Text5a","Text6a"},$T:$T,{"Text1b","Text2b"}))

If I remove one of the arrays it works absolutely fine, but I do definitely need both arrays.

Can any ninjas please offer some assistance with how to perform this multiple array formula?

Many thanks guys and gals for your help.

 

[JawaharPrem]

Hi,

can you share the sample file. it will be easy...

thanks
jawahar prem

 

[Haseeb A]

Hello JC,

if you have two arrays in the criteria field of a COUNTIFS/SUMIFS, one of the array must be in semi-colon (cannot use more than two). So use a semi-colon (;) to separate the 2nd array.

=SUM(COUNTIFS($E:$E,"19/07/2017",$P:$P,"<3",$B:$B,">14",$N:$N,{"Text1a","Text2a","Text3a","Text4a","Text5a","Text6a"},$T:$T,{"Text1b";"Text2b"}))

See the Red highlighted