Merge two SUM(COUNTIFS( formulas

[detriez]

These two formulas work independently but I cant figure out how to put them together
I'm trying to count the status values AND Countries

=SUM(COUNTIFS(Table6[Lead Status],{"Open","Suspect","Working - Attempted","Working - Connected"}))

=SUM(COUNTIFS(Table6[Country],{"United States","Canada"}))

I tried this but, the counts are wrong

=COUNTIFS(Table6[Lead Status],{"Open","Suspect","Working - Attempted","Working - Connected"},Table6[Country],{"United States","Canada"})

 

[Fluff]

Try
=sum(COUNTIFS(Table6[Lead Status],{"Open","Suspect","Working - Attempted","Working - Connected"},Table6[Country],{"United States";"Canada"}))

Note the ; in the second array

 

[detriez]

Thanks Fluff.. That worked by why? What does the ; do and why would it only be in the last array?

 

[Fluff]

When working with two arrays like that one has to be horizontal(rows) & one Vertical (columns)then ; in the 2nd array makes it vertical

 

[detriez]

ok.. thanks.
so using that, this should also work?

=SUM(COUNTIFS(Table6[Zip/Postal Code],"<>"&"*",Table6[Country],{"United States";"Canada"}))

 

[Fluff]

Did you try it?

 

[detriez]

Yeah.. No. that didn't work.. Maybe this not an array
but , instead of ; didnt work either

"<>"&"*"

 

[Fluff]

If you are trying to count blanks try "" instead of "<>"&"*"

 

[detriez]

Tried that too... But again the numbers are off

=SUM(COUNTIFS(Table6[Zip/Postal Code],"",Table6[Country],{"United States","Canada"}))

 

[detriez]

my bad.. I forgot to include the "Status" Array.. It all works now

Thank you for your help