Sumifs and Countifs limitations with ors and AND: Excel formulas:

pedie

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Hi, below is the current excel formula.
Is there any limitations with Sumifs and Countifs with or combinations
'=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC";"ORANGE"},A2:A27
,{"NY","GA","LA"}))

for exampel if I want it this way {"NY","GA","LA","AZ","TX"})), will it still give me accurate answer?


Thanks in advance.:)
 
Sort by date Sort by votes
Aladin, thank you very very much for helping me out and for educating me....

So, do you mean, using either H,V or V,H way will yeild same result?

Thanks again, sorry for bring back but i think i'm kinda confused, because i didnt even know h,v thing existed..:confused:

:)

Aladin Akyurek said:
Testing which involves two sets of criteria requires a V,H or H,V organization... V stands for vertical and H for horizontal.
Click to expand...
Aladin Akyurek said:

V,H as in:

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC";"ORANGE"},A2:A27,{"NY","GA","LA","AZ","TX"}))

yields an intermeadiate result like:

SUM({1,0,1,0,1;0,1,0,0,0})

H,V as in:

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC","ORANGE"},A2:A27,{"NY";"GA";"LA";"AZ";"TX"}))

yields an intermeadiate result like:

=SUM({1,0;0,1;1,0;0,0;1,0})

whereas...

V,V and H,H as in:

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC";"ORANGE"},A2:A27,{"NY";"GA";"LA";"AZ";"TX"}))

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC","ORANGE"},A2:A27,{"NY","GA","LA","AZ","TX"}))

yield the following intermediate results, respectively:

=SUM({1;1;0;0;0})

=SUM({1,1,0,0,0})

Using a cell range instead of an array constant does not show a different behavior.

Concluding...

I guess H,V or V,H are more often than not the intended behavior.
Click to expand...

 
Upvote 0

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Matty said:
Hi Aladin,

Thanks for sharing your thoughts.

With array constants such as those pedie is working with, given that they are being applied to Columns of data, I suppose it doesn't matter whether the "," or the ";" delimiter is used, as long as the approach is consistent. Or am I missing something here?

Matty
Click to expand...

Matty

It should be either:

H,V or V,H,

but not: H,H or V,V.
 
Upvote 0
My tab. figures are as below;

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; WIDTH: 48pt; HEIGHT: 12pt; BORDER-TOP: #953735 2pt double; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl65 height=16 width=64> </TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; WIDTH: 48pt; BORDER-TOP: #953735 2pt double; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl66 width=64>
A​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; WIDTH: 48pt; BORDER-TOP: #953735 2pt double; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl66 width=64>
B​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; WIDTH: 48pt; BORDER-TOP: #953735 2pt double; BORDER-RIGHT: #953735 2pt double" class=xl67 width=64>
C​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
1​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
LOCATION​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
MONTHS​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
DEPT.​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
2​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
LA​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
MAY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
FEC​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
3​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
LA​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
JUNE​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
ORANGE​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
4​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
TX​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
JULY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
OPSRING​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
5​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
NY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
AUGUST​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
FEC​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
6​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
LA​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
SEPTEMBER​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
ORANGE​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
7​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
GA​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
JANUARY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
OPSRING​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
8​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
GA​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
FEBRUARY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
FEC​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
9​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
FL​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
MARCH​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
ORANGE​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
10​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
GA​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
APRIL​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
OPSRING​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
11​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
NY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
MAY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
FEC​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
12​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
AZ​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
JUNE​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
ORANGE​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
13​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
NY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
JULY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
FEC​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
14​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
NY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
April​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
FEC​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
15​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
NY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
JANUARY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
FEC​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
16​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
OK​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
FEBRUARY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
ORANGE​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
17​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
AZ​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
MARCH​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
FEC​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
18​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
OK​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
APRIL​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
FEC​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
19​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
OK​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
MAY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
ORANGE​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
20​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
FL​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
JANUARY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
OPSRING​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
21​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
AZ​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
FEBRUARY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
FEC​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
22​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
OK​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
MARCH​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
FEC​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
23​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
OK​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
APRIL​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
ORANGE​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
24​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
AZ​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
MAY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
OPSRING​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
25​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
OK​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
JUNE​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
FEC​
</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 11.25pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl68 height=15>
26​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
KS​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl69>
JULY​
</TD><TD style="BORDER-BOTTOM: #c5d9f1 0.5pt solid; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl70>
ORANGE​
</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #953735 2pt double; BORDER-LEFT: #953735 2pt double; BACKGROUND-COLOR: #92d050; HEIGHT: 12pt; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl71 height=16>
27​
</TD><TD style="BORDER-BOTTOM: #953735 2pt double; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl72>
OK​
</TD><TD style="BORDER-BOTTOM: #953735 2pt double; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #c5d9f1 0.5pt solid" class=xl72>
April​
</TD><TD style="BORDER-BOTTOM: #953735 2pt double; BORDER-LEFT: #c5d9f1; BACKGROUND-COLOR: #92d050; BORDER-TOP: #c5d9f1; BORDER-RIGHT: #953735 2pt double" class=xl73>
OPSRING​
</TD></TR></TBODY></TABLE>​
 
Upvote 0
Matty

It should be either:

H,V or V,H,

but not: H,H or V,V
Click to expand...
OK, but why is this necessary? The logic doesn't seem particulary intuitive (to me at least!) given that the ranges are consistent, i,e, Columns.

And what happens if there are, say, three array constants? Is it then V,H,V or H,V,H?

Thanks,

Matty
 
Upvote 0
Matty,

When one array is vertical and the other is horizontal the result is a matrix whose size is the width of the horizontal range and the height of the vertical one, e.g. in this formula

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC","ORANGE"},A2:A27,{"NY";"GA";"LA";"AZ";"TX"}))

the COUNTIFS part returns an array which is 2 columns by 5 rows, effectively every combination of one value from one array and one from the other. SUM sums that array.

If you use V V or H H then the results won't be correct (unless by some fluke) because you won't be including all combinations.

For three arrays you need a different approach, e.g. SUMPRODUCT with ISNUMBER(MATCH as I suggested here
 
Last edited:
Upvote 0
Thanks everyone once again for clearing up things for me....
This helped alot.:)
 
Upvote 0
barry houdini said:
Matty,

When one array is vertical and the other is horizontal the result is a matrix whose size is the width of the horizontal range and the height of the vertical one, e.g. in this formula

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC","ORANGE"},A2:A27,{"NY";"GA";"LA";"AZ";"TX"}))

the COUNTIFS part returns an array which is 2 columns by 5 rows, effectively every combination of one value from one array and one from the other. SUM sums that array.

If you use V V or H H then the results won't be correct (unless by some fluke) because you won't be including all combinations.

For three arrays you need a different approach, e.g. SUMPRODUCT with ISNUMBER(MATCH as I suggested here
Click to expand...

Right...
 
Upvote 0
Matty,

When one array is vertical and the other is horizontal the result is a matrix whose size is the width of the horizontal range and the height of the vertical one, e.g. in this formula

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC","ORANGE"},A2:A27,{"NY";"GA";"LA";"AZ";"TX"}))

the COUNTIFS part returns an array which is 2 columns by 5 rows, effectively every combination of one value from one array and one from the other. SUM sums that array.

If you use V V or H H then the results won't be correct (unless by some fluke) because you won't be including all combinations.

For three arrays you need a different approach, e.g. SUMPRODUCT with ISNUMBER(MATCH as I suggested here
Click to expand...
Hi barry houdini,

Thanks for responding.

I get the ISNUMBER(MATCH concept - in fact, I suggested a similar set up earlier in this thread, i.e.:

Code: 
=SUM(IF(B2:B27="May",IF(ISNUMBER(MATCH(C2:C27,{"FEC","ORANGE"},0)),IF(ISNUMBER(MATCH(A2:A27,{"NY","GA","LA","AZ","TX"},0)),1))))
I can run this formula through the formula evaluator and it makes perfect sense to me. But I am really struggling to understand how the resulting array comes about with this formula:

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC","ORANGE"},A2:A27,{"NY";"GA";"LA";"AZ";"TX"}))

That array being:

{1,0;0,0;1,0;0,0;0,0}

Could you help a simple fellow understand this more clearly? I just can't see how it comes about given pedie's data set.

Thanks,

Matty
 
Upvote 0
Matty said:
Hi barry houdini,

Thanks for responding.

I get the ISNUMBER(MATCH concept - in fact, I suggested a similar set up earlier in this thread, i.e.:

Code: 
=SUM(IF(B2:B27="May",IF(ISNUMBER(MATCH(C2:C27,{"FEC","ORANGE"},0)),IF(ISNUMBER(MATCH(A2:A27,{"NY","GA","LA","AZ","TX"},0)),1))))
I can run this formula through the formula evaluator and it makes perfect sense to me. But I am really struggling to understand how the resulting array comes about with this formula:

=SUM(COUNTIFS(B2:B27,"May",C2:C27,{"FEC","ORANGE"},A2:A27,{"NY";"GA";"LA";"AZ";"TX"}))

That array being:

{1,0;0,0;1,0;0,0;0,0}

Could you help a simple fellow understand this more clearly? I just can't see how it comes about given pedie's data set.

Thanks,

Matty
Click to expand...

Consider A2:B7...

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>GA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ORANGE</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>LA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>TX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>AZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>XZ</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>TX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>

Goal: We want to do a record count where A2:A7 matches any of < NY | GA |LA | AZ | TX > and B2:B7 < FEC | ORANGE >. Using | as separator here intended. Let's define...

< NY | GA |LA | AZ | TX > as the longer criteria set; and

< FEC | ORANGE > as the shorter criteria set.

1. Let's take up the V (shorter criteria set),H (longer criteria set) case...

=SUMPRODUCT(COUNTIFS(B2:B7,{"FEC";"ORANGE"},A2:A7,{"NY","GA","LA","AZ","TX"}))

This appears to be worked like this...

<TABLE style="WIDTH: 528pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=704><COLGROUP><COL style="WIDTH: 48pt" span=11 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>GA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>LA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>AZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>TX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>4</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>GA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ORANGE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>ORANGE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>GA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>LA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>AZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>TX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>LA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>TX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>AZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>XZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>TX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>

Note that for every vertical criterion value the horizontal set is repeated.

K2, copied down:

=SUMPRODUCT(COUNTIFS($B$2:$B$7,D2,$A$2:$A$7,E2:I2))

We have in K2:

=SUMPRODUCT({1,0,1,0,2})

which shows how often a value in {"NY","GA","LA","AZ","TX"} is associated with FEC;

Similarly, in K3:

=SUMPRODUCT({0,1,0,0,0})

shows the association with ORANGE.

2. Let's reverse (1), that is, let's take up V (longer criteria set) and H (shorter criteria set) ...

<TABLE style="WIDTH: 528pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=704><COLGROUP><COL style="WIDTH: 48pt" span=11 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>ORANGE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>GA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ORANGE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>GA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>ORANGE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>LA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>LA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>ORANGE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>TX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>AZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>ORANGE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>AZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>XZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>TX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>ORANGE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>TX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>

K2, copied down:


=SUMPRODUCT(COUNTIFS($A$2:$A$7,D2,$B$2:$B$7,E2:F2))

We have now in K2:

=SUMPRODUCT({1,0})

which shows how often a value in {"FEC","ORANGE"} is associated with NY;

Similarly, in K3:


=SUMPRODUCT({0,1})


shows the association with GA; And so on.

One set vertical and other horizontal allows Excel to construct a pairwise AND evaluation. For doing an AND evaluation, Excel apparently expects this arrangement.

3. Let's take up H (shorter criteria set) and H (longer criteria set) ...

<TABLE style="WIDTH: 528pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=704><COLGROUP><COL style="WIDTH: 48pt" span=11 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>ORANGE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>NY</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>GA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>LA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>AZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>TX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>GA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>ORANGE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>LA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>TX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>AZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>XZ</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>TX</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>FEC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>

K2:

=SUMPRODUCT(COUNTIFS($B$2:$B$7,D2:E2,$A$2:$A$7,F2:J2))

We have now in K2:


=SUMPRODUCT({1,1,0,0,0})

which shows the results of ANDing FEC and NY plus ORANGE and GA. The values in H2:J2 cannot be ended, hence 0 values.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
IF/MATCH statement that includes the SUM
Replies
5
Views
315
Fluff
Fluff
R
  • Solved
Combination Sumif and/or Countif on a condition
Replies
3
Views
321
Fluff
Fluff
K
  • Question Question
sumproduct+countif+sumif
2
Replies
11
Views
642
KRice
K
J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
45
Fluff
Fluff
V
  • Question Question
Solve #VALUE errors of average formula, when the analyzed cell contains strings of length 0
Replies
1
Views
406
kvsrinivasamurthy
K

Forum statistics

Threads
1,223,276
Messages
6,171,139
Members
452,381
Latest member
Nova88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top