Countifs and Or combination: Need excel formula help please...

[pedie]

Hi, I have the current formula from yesterday's thread for sumif and or combination; but I need it for Countifs and Or combination too.

I want to count if col A, has "NY","LA" or "GA"
and col D has "May"
and Col C has "FEC" or "ORANGE"

Thanks in advance.

'SUM(SUMIFS($D$1:$D$27,$B$1:$B$27,"May",$C$1:$C$27,{"FEC","ORANGE"}))

 

[Ron Coderre]

Does this approach work for you?

=SUM(COUNTIFS($A$1:$A$27,{"NY","LA","GA"},$D$1:$D$27,"May",$C$1:$C$27
,{"FEC";"ORANGE"}))

 

[pedie]

Ron, thanks! it is not returing desired result... I want the count if the critiria is met...
I think the answer should be 4

 

[pedie]

My apologies Ron, it is perfect! it is giving me the correct result...

can i ask one more thing...
What is the difference in the formula you provided to the one below; i thought it is same but diff result;

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

Do i have to enter formula in col alphabatical order?

 

[T. Valko]

Ron, thanks! it is not returing desired result... I want the count if the critiria is met...
I think the answer should be 4

<TABLE style="WIDTH: 141pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=188 border=0><COLGROUP><COL style="WIDTH: 10pt; mso-width-source: userset; mso-width-alt: 475" width=13><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" span=2 width=55><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 10pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=13 height=15>

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 49pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=65>A

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=55>B

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=55>C

​

</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>1

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LOCATION

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">MONTHS

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">DEPT.

​

</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>2

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LA

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">May

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">FEC

​

</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>3

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LA

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">May

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ORANGE

​

</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>4

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LA

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">May

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">OPSRING

​

</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>5

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LA

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">May

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">FEC

​

</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15>6

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">LA

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">May

​

</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ORANGE

</TD></TR></TBODY></TABLE>

Ron's formula does what you want.

You just have to get the ranges in order. You said:

I want to count if col A, has "NY","LA" or "GA"
and col D has "May"
and Col C has "FEC" or "ORANGE"

But in your other post you have the columns as A, B and C.

 

[Ron Coderre]

There an ambiguity between the column references asked for and what your sample data.

Using the sample data, try this regular formula:

=SUM(COUNTIFS($A$1:$A$27,{"NY","LA","GA"},$B$1:$B$27,"May",$C$1:$C$27
,{"FEC";"ORANGE"}))

Does that help?

 

[Ron Coderre]

My apologies Ron, it is perfect! it is giving me the correct result...

can i ask one more thing...
What is the difference in the formula you provided to the one below; i thought it is same but diff result;

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

Do i have to enter formula in col alphabatical order?

I'm having a heck of a time trying to decipher your posts!
Did you mean to reference "LA" twice?
If NO...then, this version of your formula would return: 4

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

Notice that FEC and ORANGE are separated by semicolons, not commas.
That subtle change causes them to be treated like separate rows, resulting in the correct array structure needed to calculate the results.

 

[pedie]

Ron, duplicate "LA" was typo error..sorry again for confusion.
'=SUM(COUNTIFS($A$1:$A$27,{"NY","LA","GA"},$B$1:$B$27,"May",$C$1:$C$27,{"FEC";"ORANGE"}))

Can you please clarify why we have ";" between fec and orange and not between "NY","LA" and "GA"?

 

[Ron Coderre]

I'm not sure I can succinctly and effectively describe the reason.

Try this to get you familiar with what's happening...
using your posted sample data
and
H1: =A1:C6

• Select H1
• Click in the formula bar
• Press the F9 key to resolve the formula.

You see this:
{"LOCATION","MONTHS","DEPT.";"LA","May","FEC";"LA","May","ORANGE";
"LA","May","OPSRING";"LA","May","FEC";"LA","May","ORANGE"}

Notice that columns are separated by commas
and rows are separated by semicolons.

 

[pedie]

Ron, thanks again soo much for helping me out....!!!

Goodnight!